Sounds like your module is headed Option Explicit (good) which means any
routine that attempts to use an undeclared variable will halt. In the code I
posted I forgot to declare sRep!

Change
Dim sOld As String, sNew
to
Dim sOld As String, sNew As String, sRep As String

Regards,
Peter T

"Monk" wrote in message
...
Thanks for your assistance Peter

I am getting a variable not defined error on the "sRep". Can you advise
how
to fix this as well?

Regards

"Peter T" wrote:

Replace is n/a in Excel 07, along with various other string manipulation
functions. To cater for all versions do it like this

Dim sOld As String, sNew
'code

sOld = cell.Value
#If VBA6 Then ' Excel 2000 or later
sNew = Replace(Replace(Replace(sOld, "~", "~~"), "*", "~*"), "?",
"~?")
#Else ' Excel 97
With Application.WorksheetFunction
sRep = .Substitute(.Substitute(.Substitute(sOld, _
"~", "~~"), "*", "~*"), "?",
"~?")
End With
#End If
My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & sNew


Regards,
Peter T

"Monk" wrote in message
...
I get an error on the Replace part of the following code for people who
are
still using Excel 97. I have tried using substitute instead but this
also
does not seem to work. Any suggestions would be appreciated.

My_Range.AutoFilter Field:=FieldNum, Criteria1:="=" & _

Replace(Replace(Replace(cell.Value, "~", "~~"), "*", "~*"), "?", "~?")