replace doesn't

[David]

In XL2000, I use the following sub to update some stuff each month, but the
Replace portion doesn't replace anything

Cells B42 and G42 contain formulas that link to cells in 'Foodcost.xls'

No errors occur, but it's probably some simple syntax problem I can't
solve.

Any help?

Sub Renew()
Dim OldMonth As String
Dim NewMonth As String
Dim wkbk As Workbook
Set wkbk = ActiveWorkbook
Application.ScreenUpdating = False
Workbooks.Open "Foodcost.xls"
wkbk.Activate
Range("Initial_Qty").Value = Range("On_Hand").Value
Range("Added_Used").ClearContents
OldMonth = Workbooks("Foodcost.xls").Sheets(5).Name 'i.e. Dec
NewMonth = Workbooks("Foodcost.xls").Sheets(6).Name 'i.e. Jan
Range("B42,G42").Cells.Replace What:=OldMonth, Replacement:=NewMonth,
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Workbooks("Foodcost.xls").Close
Application.ScreenUpdating = True
End Sub

 

[Sharqua]

David,

Did you mean for this to be all of the columns from B to
G in Row 42?

Try "B42:G42" rather than "B42,G42"

-Donna

 

[David]

Sharqua wrote

Did you mean for this to be all of the columns from B to
G in Row 42?

No. I only wanted to look at two cells (B42 and G42)... however:

Range("B42,G42").Cells.Replace What:=OldMonth, Replacement:=NewMonth,

Doh! Discovered I was referencing the wrong row! Changed to 41 and all is
well Forgot I had recently deleted a row after macro was written.

Thanks for jumping in, though.

 

[Don Guillett]

Name the cells and refer to the name and that won't happen again

 

[David]

Don Guillett wrote

Name the cells and refer to the name and that won't happen again

Done. Thanks.