[RESOLVED] Excel VBA WorksheetFunction.Find - Runtime Error 1004

[tepedekoy]

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

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

[westconn1]

are you sure that you are looking at the correct worksheet?

Title = Range("C9").Value
   msgbox title
    On Error GoTo ExportXML:
    Illegal = WorksheetFunction.Find("&", Title)

or you can handle the error like

Dim Title As String, NewTitle As String
    Title = Range("a2").Value
On Error Resume Next
Illegal = WorksheetFunction.Find("&", Title)
If Not Err.Number = 0 Then MsgBox "& not found in " & vbNewLine & Title: Exit Sub
On Error GoTo 0
 NewTitle = Mid(Title, 1, Illegal - 1) & "and" & Mid(Title, Illegal + 1)

[tepedekoy]

Thanks Westconn,

I tried your suggestions, but was still getting the same error;
Runtime Error 1004: unable to get the find property of the worksheetfunction class

In the end I have used the InStr function and this seems to be working. A smaple of my working code is below.

Thanks for your help.

TitleCharTest: 'Test Title to replace any '&' with 'and'
    Sheet7.Select
    Dim Title As String, NewTitle As String
    Title = Range("C9").Value
    Illegal = InStr(1, Title, "&")
    If Illegal = 0 Then GoTo ExportXML:
    NewTitle = Mid(Title, 1, Illegal - 1) & "and" & Mid(Title, Illegal + 1)
    Range("C9").Value = NewTitle
    GoTo TitleCharTest: