W
WLMPilot
Two questions:
1) I am trying to replace 2008 within several formulas with 2009. Both
2008 and 2009 are worksheet names and appear in the formulas such as the
following:
[Paychecks.xls]2008'!$B$10 (part of formula)
I have looked at two different ways and need help on choosing the best
method AND if I need to adjust the macro code to make it work correctly.
Method 1:
Range("S5:X705").Replace _
What:="2008", Replacement:="2009", _
SearchOrder:=xlByRows, MatchCase:=True
Not sure if quotes are neede in the above example.
Method 2:
Range("S5:X705").Select
For Each cell In Selection
cell.Formula = Replace(cell.Formula, "2008", "2009")
Next cell
Question 2) I have executed the code shown in Method 2. Problem is that
since the formula references another workbook, it opens a window (displaying
contents of folder (directory) asking to update the workbook referenced in
the formula. How can I stop that part from happening?
Thanks,
Les
1) I am trying to replace 2008 within several formulas with 2009. Both
2008 and 2009 are worksheet names and appear in the formulas such as the
following:
[Paychecks.xls]2008'!$B$10 (part of formula)
I have looked at two different ways and need help on choosing the best
method AND if I need to adjust the macro code to make it work correctly.
Method 1:
Range("S5:X705").Replace _
What:="2008", Replacement:="2009", _
SearchOrder:=xlByRows, MatchCase:=True
Not sure if quotes are neede in the above example.
Method 2:
Range("S5:X705").Select
For Each cell In Selection
cell.Formula = Replace(cell.Formula, "2008", "2009")
Next cell
Question 2) I have executed the code shown in Method 2. Problem is that
since the formula references another workbook, it opens a window (displaying
contents of folder (directory) asking to update the workbook referenced in
the formula. How can I stop that part from happening?
Thanks,
Les