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

 

[erik.van.geit]

you can use Sendkeys for this, but if we can avoid that, it would be better

what is the replacement you want to do in the code ?
showing the line which needs to be changed, could provide some thoughts of another route to go ...

best regards,
Erik

 

[fmkjr]

I was planning on deleting the module then copying over an updated module. But if we can change two lines, that would work too...


The code to change is in a module called populateData

Sub populateINTL()
Worksheets("International Performance").Range("e8").Value = Worksheets("Intl_Data").Range("b3").Value
Worksheets("International Performance").Range("e7").Value = Worksheets("Intl_Data").Range("c3").Value
Worksheets("International Performance").Range("e9").Value = Worksheets("Intl_Data").Range("f3").Value

End Sub

The first two lines need to be changed;
.range("e8") should be ("e7")
.range("e7") should be ("e8")

thanks
frank

 

[erik.van.geit]

this should be rather simple
you could refer to a specific cell to get the address (kinda INDIRECT function )

example
say A1 houses the text F5
with this code

Range(Range("A1")) = "hello"

you will put hello in cell F5

so if you want to change references, just write to the "reference"cell

tips:
you can use a named range

Range(Range("reference_cell")) = "hello"

you would better hide this cell


applied to your code

Worksheets("International Performance").Range("reference_cell").Value = Worksheets("Intl_Data").Range("b3").Value

best regards,
Erik