I have written a macro to initialize a worksheet that is copied. There are
three elements that need to be changed throughout the worksheet for the
upcoming year:

1) Change caption on commandbuttons to show 2009
2) Change the date for each of the 12 budgets to correct year
3) change the 2008 to 2009 in the formulas in some of the cells.

Elements 1 and 2 are working, but I cannot figure out how to look in a
formula in a cell for the value 2008 and change it to 2009.

This is what I had, but it did not work:

Range("S5:X705).Select
For Each cell In Selection
cell.Value = Replace(cell.Value, "2008", "2009")
Next cell

I copied this from a website I found on replacing a value.

Thanks for your help,
Les

You need to replace in Cell.Formula not Cell.Value
Try
Sub test()
Range("S5:X705").Select
For Each cell In Selection
cell.Formula = Replace(cell.Formula, "2008", "2009")
Next cell
End Sub

Try the sample code below to understand
Sub t()
Dim str As String
MsgBox Range("A1").Formula
str = Range("A1").Formula
str = Replace(str, "2008", "2009")
Range("A1").Formula = str
MsgBox Range("A1").Formula
End Sub
"WLMPilot" wrote:

I have written a macro to initialize a worksheet that is copied. There are
three elements that need to be changed throughout the worksheet for the
upcoming year:

1) Change caption on commandbuttons to show 2009
2) Change the date for each of the 12 budgets to correct year
3) change the 2008 to 2009 in the formulas in some of the cells.

Elements 1 and 2 are working, but I cannot figure out how to look in a
formula in a cell for the value 2008 and change it to 2009.

This is what I had, but it did not work:

Range("S5:X705).Select
For Each cell In Selection
cell.Value = Replace(cell.Value, "2008", "2009")
Next cell

I copied this from a website I found on replacing a value.

Thanks for your help,
Les