Unable to get the Search property of the WorksheetFunction class.

[rjn2002]

I am having trouble with IsError function consistently catching errors in my call to the Search worksheetfunction. For example, here is a snippet of my code that works just fine:

If IsError(Application.WorksheetFunction.Search("__*-DEFERRED", netName)) Then
            If IsError(Application.WorksheetFunction.Search("__* Due", netName)) Then
                If IsError(Application.WorksheetFunction.Search(" TOTDUEINT ", netName)) Then
                    If IsError(Application.WorksheetFunction.Search(" ARREARS ", netName)) Then...

However, later in my code I have:

tempNetName = UCase(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR)))
    
    If IsError(Application.WorksheetFunction.Search(" DUE FROM ", tempNetName)) Then...

This gives me the following error: "Run-time error '1004': Unable to get the Search property of the WorksheetFunction class."

Furthermore, here is the entire piece of code that is giving me problems currently. I have commented out the line where I use the InStr function, which works fine. Then all of the following calls to the Search function work fine as well; however in its current state, I get the error mentioned above. The variables "tempNetName" and "netName" are both earlier defined as Strings and are returning valid strings to be evaluated.

    If IsError(Application.WorksheetFunction.Search(" DUE FROM ", tempNetName)) Then
    'If InStr(1, tempNetName, " DUE FROM ", vbTextCompare) = 0 Then
            'do nothing
    Else
        netName = ReturnLastWord(Sheet1.Range("netLIBOR").Offset(yG, xR))
        edgeName = Application.WorksheetFunction.VLookup(netName, Sheet3.Range("tokenTable"), 2, 0)
        lineType = "DUE"
        endOfCode = " Due from " & edgeName
        netName = UCase(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR)))
        If IsError(Application.WorksheetFunction.Search("__*-DEFERRED", netName)) Then
            If IsError(Application.WorksheetFunction.Search("__* Due", netName)) Then
                If IsError(Application.WorksheetFunction.Search(" TOTDUEINT ", netName)) Then
                    If IsError(Application.WorksheetFunction.Search(" ARREARS ", netName)) Then
                        'If IsError(Application.WorksheetFunction.Search(" PRINCIPAL ", netName)) Then
                        'must be principal
                        edgeName = "T_" & ReturnFirstWord(netName) & "_Principal" & endOfCode
                    Else
                        'if it's Arrears
                        edgeName = "T_" & ReturnFirstWord(netName) & "_i_Deferred" & endOfCode
                    End If
                Else
                    'totdueint
                    edgeName = "T_" & ReturnFirstWord(netName) & "_i_Paid" & endOfCode
                End If
            Else
                'it's hedge or fee due. currently assumes a fee
                edgeName = "F_" & Mid(netName, 3, Application.WorksheetFunction.Search(" Due", netName) - 3) & "_Paid" & endOfCode
            End If
        Else
            'fee or hedge deferred.  currently assumes a fee
            edgeName = "F_" & Mid(netName, 3, Application.WorksheetFunction.Search("-Deferred", netName) - 3) & "_Deferred" & endOfCode
        End If
            
    End If

I know that it is not good to rely on worksheet functions too much, but this is more of a general question as to why it's not working consistently all through my code.

 

[RoryA]

Glad to help. Just goes to show that although On Error Resume Next can be useful, it's also a dangerous beastie!