Replacing via macro

[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

 

[Dave Peterson]

When I get that dialog box, it means that the filename (or sheet within that
workbook) I changed to doesn't really exist. So excel is asking where the real
file (or worksheet) is.

Are you sure that the filenames only difference by that year string?

===
Once you've corrected that problem, then I would think the edit|Replace version
would be much quicker than looping through all the cells.

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

 

[WLMPilot]

I have the budget and paycheck workbook on a flash drive that I keep with me
and edit the data as needed. When I am home, I update the copies on the
desktop. I use the workbooks on the desktop to update each other and then
copy back to the flash drive. That is why it cannot find the file.

Les

When I get that dialog box, it means that the filename (or sheet within that
workbook) I changed to doesn't really exist. So excel is asking where the real
file (or worksheet) is.

Are you sure that the filenames only difference by that year string?

===
Once you've corrected that problem, then I would think the edit|Replace version
would be much quicker than looping through all the cells.

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