Problems ReplaceFormat - error 1004
 

I have a load of workbooks within which I need to change any cells
formatted with dollars to pounds. I have written this macro to do it:

Sub ChangeDollarsToPounds()

Dim Filename As String
Dim wb As Workbook
Dim ws As Worksheet

Filename = Dir("C:\Templates\*.xls")
Do While Filename < ""
Set wb = Workbooks.Open("C:\Templates\" & Filename)
Set ws = wb.Worksheets("Data")

Application.FindFormat.NumberFormat = "[$$-409]#,##0"
Application.ReplaceFormat.NumberFormat = "[$
£-809]#,##0" '**** code crashes here ****
ws.Cells.Replace What:="", Replacement:="", LookAt:=xlPart,
SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True,
ReplaceFormat:=True

wb.Save
wb.Close

Filename = Dir()
Loop

End Sub

....however, the code always crashes with Error 1004 : Application or
Object defined error at the line shown. This code was lifted directly
from a recorded macro, but it seems that if I try to use it in any
workbooks other than the one it was recorded in it crashes at that
line.

I even tried copying the recorded code directly into a blank workbook
and running it and it also failed at the same place.

Any advice would be gratefully received...

Problems ReplaceFormat - error 1004
 

I'm not sure, but have you tried changing "Application" to "ws" in the code.

"ndsykes" wrote:

I have a load of workbooks within which I need to change any cells
formatted with dollars to pounds. I have written this macro to do it:

Sub ChangeDollarsToPounds()

Dim Filename As String
Dim wb As Workbook
Dim ws As Worksheet

Filename = Dir("C:\Templates\*.xls")
Do While Filename < ""
Set wb = Workbooks.Open("C:\Templates\" & Filename)
Set ws = wb.Worksheets("Data")

Application.FindFormat.NumberFormat = "[$$-409]#,##0"
Application.ReplaceFormat.NumberFormat = "[$
£-809]#,##0" '**** code crashes here ****
ws.Cells.Replace What:="", Replacement:="", LookAt:=xlPart,
SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=True,
ReplaceFormat:=True

wb.Save
wb.Close

Filename = Dir()
Loop

End Sub

....however, the code always crashes with Error 1004 : Application or
Object defined error at the line shown. This code was lifted directly
from a recorded macro, but it seems that if I try to use it in any
workbooks other than the one it was recorded in it crashes at that
line.

I even tried copying the recorded code directly into a blank workbook
and running it and it also failed at the same place.

Any advice would be gratefully received...

Problems ReplaceFormat - error 1004
 

Unfortunately, the FindFormat and ReplaceFormat properties only apply
to the Application object, so can't be changed to the worksheet object
in that way.