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...

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...

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