Suggestion needed replacing one formula on a distributed WB

[fmkjr]

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

 

[hatman]

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:

    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]

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]

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

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]

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]

Is this sheet visible when your code is "selecting" it ?

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

 

[fmkjr]

sheet is visible

Yes, the sheet is visible.

thanks!
frank

 

[GlennUK]

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]

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]

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!