Hi there,

I am having problems with the FIND & SEARCH commands in Excel. I am trying to find "&" in the cell C9 and if found replace it with "and".

While the character is there to be found I have no problem, but I am looping through the code, in case there are multiple instances. When the character is not there to be found I get "Runtime Error 1004".

Code:
TitleCharTest: 'Test Title to replace any '&' with 'and'
    Sheet7.Select
    Dim Title As String, NewTitle As String
    Title = Range("C9").Value
    On Error GoTo ExportXML:
    Illegal = WorksheetFunction.Find("&", Title)
    On Error GoTo 0
    NewTitle = Mid(Title, 1, Illegal - 1) & "and" & Mid(Title, Illegal + 1)
    Range("C9").Value = NewTitle
    GoTo TitleCharTest:
I have the "On Error GoTo ExportXML:" line located before the search with the plan that when this error occurs the program should jump to the label ExportXML: and continue.

Any help on why I am doing getting this error would be appreciated.

Cheers,
Tepe