Suggestion needed replacing one formula on a distributed WB

fmkjr

Board Regular
Joined
Jun 4, 2006
Messages
68
What would be the best way to replace a formula on a workbook that has been distributed to aprox. 1000 users without resending the sheet? I would like to send some sort of "patch" to the users which would replace the formula in the sheet without changing the data throughout the rest of the workbook.

The average end user is technically challenged.

Would it be a worksheet when opened prompted the user to open the other WB (the one that needs corrected) which would then navigate to the WS that holds the formula, turns off the protection, replaces the old formula in the cell, turns on protection, then saves the WB?

thanks
frank
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Yup, that would work... the only thing I would do differently is to use the GetOpenFilename() function to open the workbook to be fixed... it will make your life much easier that way:

Code:
    dim wb as workbook
    dim FileToOpen as Variant

    FileToOpen = Application.GetOpenFilename(Title:="Please choose a file to Fix", FileFilter:="Excel Files *.xls (*.xls),")

    If FileToOpen = False Then

        MsgBox "No file specified.", vbExclamation, "Duh!!!"

        Exit Sub

    End If
    set wb = workbooks.open(Filename:=FileToOpen)

Then you can manipulate the WB object directly...
 

fmkjr

Board Regular
Joined
Jun 4, 2006
Messages
68
one more ?

thanks for the help...

I have another ?... I am attempting to write this formula into the sub but I continue to get an error:

Compile error
expected end of statement with "Incomplete" highlighted

ActiveCell.FormulaR1C1 = "=IF(D24="","Incomplete",(D24*1/3)+(IF(D25>0, 0, 100)*1/3)+(IF(D26>1, 0, 100)*1/3))"

I tried activecell.value, activecell.formula
They all bring the same results... an error

thanks
frank
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Compile error
expected end of statement with "Incomplete" highlighted

ActiveCell.FormulaR1C1 = "=IF(D24="","Incomplete",(D24*1/3)+(IF(D25>0, 0, 100)*1/3)+(IF(D26>1, 0, 100)*1/3))"
Hi, Frank,
you need to double the doublequotes :)
you're defining the formula by means of a string
formula = "........."
using a single doublequote will "close" the string, hence the error ...
just a matter of syntax
again about syntax: use "Formula" instead of "FormulaR1C1": see helpfiles for more info
Code:
ActiveCell.Formula = "=IF(D24="""",""Incomplete"",(D24*1/3)+(IF(D25>0, 0, 100)*1/3)+(IF(D26>1, 0, 100)*1/3))"

kind regards,
Erik
 

fmkjr

Board Regular
Joined
Jun 4, 2006
Messages
68

ADVERTISEMENT

sheet out of range

The above formula worked...

However, I cannot get the macro to run without an error. It is getting hung up on:
sheets("Hours of Service").select

I have this code immediately following the code to open the workbook.

I keep getting an out of range error.

I have a worksheet named "Hours of Service". I checked the spelling and it matches exactly.

Thanks for the help on the other two items!!!

frank

Sheets("Hours of Service").Select
ActiveSheet.Unprotect
Range("d39").Select
ActiveCell.Formula
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Is this sheet visible when your code is "selecting" it ?

posted ten words at October tenth 10:10AM local time Belgium :biggrin:
 

fmkjr

Board Regular
Joined
Jun 4, 2006
Messages
68

ADVERTISEMENT

sheet is visible

Yes, the sheet is visible.

thanks!
frank
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Make sure there aren't any trailing spaces after the sheet's actual name of "Hours of Service". You know, like is the sheet really named "Hours of Service ", or "Hours of Service "?
 

fmkjr

Board Regular
Joined
Jun 4, 2006
Messages
68
I copied the name from the properties window

I copied the name from the properties window and tried using Sheets("sheet2").Select also. Neither works. The wildest thing about all this... if I run the macro independent of the previous part of the macro (open file) it works.

I was under the assumption when the file was opened it became the active sheet. Apparently, this is not the case. Is there a way to verify if the sheet became the active sheet.

I attempted to use a variable to call the sheet too.

I tried the following:

Dim TheNewOne As String

TheNewOne = ActiveWorkbook.Name

Windows(TheBigOne).Activate
Sheets("Hours of Service").Select

thanks
frank
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
The wildest thing about all this... if I run the macro independent of the previous part of the macro (open file) it works.

... so which book is the active book when you try to do this sheet selection? It has to be the right book!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,096
Messages
5,545,926
Members
410,713
Latest member
TaremyLunsil
Top