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]

When you are not getting a run-time error, the text is being found. If you want to use IsError, you would need to use Application.Search, but Instr is better.
HTH.

 

[Norie]

Why are you using code?

What is it meant to do?

Surely you could use worksheet functions?

 

[rjn2002]

Look at the section where i have the multiple "If IsError" statements. That section works just fine because depending on the cell that I read it, it may not contain the value it's looking for and then it proceeds to check if the next search call is an error and so on. That works just fine, yet when I make the single call at the top of it, I get an error. Like I said, when I switch just the top search call to an instr (see the line I commented out), the entire thing works fine.

 

[RoryA]

Application.WorksheetFunction calls return a run-time error if they fail, not an error value. I would suspect you have an error handler in there somewhere that you have not posted.

 

[rjn2002]

I have "On Error Resume Next" in other Sub Procedures I have created. One of these subs calls the one I am having trouble with right now, but I do not have the call wrapped in the error handler. I have posted more of my code section below. I know it's not the most intelligently written, but it's what I have so far. I have just noticed that by adding the "GOTO" statements in some of my IF-statements, prevents the error from occurring. This is of course because by the time it does get to the line of code in question, it does indeed contain " DUE FROM ". The value of tempNetName and netName (netName gets assigned a few lines down) at this point is "A1 PRINCIPAL Due from __PRINCIPAL". So we get through the first search function. Now we get to a series of these statements. It properly navigates through all of these, thus they are all errors, and runs the piece of code with this comment: "'must be principal".

I know I have a working solution, but I guess I'm just wondering why it doesn't work for all cases.

Private Sub analyzeTestStep(yR, yG, s, e, yE, xEdgeAdj)

Dim netName As String, edgeName As String, tempNetName As String
xR = s
xRelative = 0


'Do Until the next Step is not the same Test Step
'Do Until IsEmpty(Sheet1.Range("netLIBOR").Offset(yG, xR)) And Sheet1.Range("netLIBOR").Offset(yG + 1, xR) <> currStep
        
    'If blank between consecutive test steps
    If IsEmpty(Sheet1.Range("netLIBOR").Offset(yG, xR)) Then
        yG = yG + 1
        GoTo lineDone
    End If
    
    'find consecutive number of steps in a row
    currStep = Sheet1.Range("netLIBOR").Offset(yG, xR)
    testCount = 0
    yTemp = yG
    Do Until IsEmpty(Sheet1.Range("netLIBOR").Offset(yTemp, xR)) And Sheet1.Range("netLIBOR").Offset(yTemp + 1, xR) <> currStep
        If Sheet1.Range("netLIBOR").Offset(yTemp, xR) = currStep Then testCount = testCount + 1
        yTemp = yTemp + 1
    Loop

    'Mid(Sheet1.Range("netLIBOR").Offset(yG, xR), Application.WorksheetFunction.Search("(Step *: TEST)", Sheet1.Range("netLIBOR").Offset(yG, xR)))
            
    'Now Loop until we've gone through all tests in this group
    For currTest = 1 To testCount
        
        'Find Next Test Step
        yTemp = yG
        Do Until IsEmpty(Sheet1.Range("netLIBOR").Offset(yTemp, xR)) And Sheet1.Range("netLIBOR").Offset(yTemp + 1, xR) <> currStep
            If Sheet1.Range("netLIBOR").Offset(yTemp, xR) = currStep Then
                yG = yTemp
                Exit Do
            End If
            yTemp = yTemp + 1
        Loop
        
        
        'set current step
        currStep = Sheet1.Range("netLIBOR").Offset(yG, xR)
        stepNumber = Mid(currStep, Application.WorksheetFunction.Search("(Step ", currStep) + 1, Application.WorksheetFunction.Search(": TEST", currStep) - (Application.WorksheetFunction.Search("(Step ", currStep) + 1))
        currStepNoBlank = Replace(UCase(Sheet1.Range("netLIBOR").Offset(yG, xR)), " ", "")
        currEdgeStepNoBlank = Replace(UCase(Sheet2.Range("edgeLIBOR").Offset(yE, xR + xEdgeAdj)), " ", "")
        'move past step name row
        yG = yG + 1
        xTemp = xR
        
    'Loop within For Loop for each Test
    Do Until IsEmpty(Sheet1.Range("netLIBOR").Offset(yG, xR))
    'Determine if it's an Available Line
    If Left(UCase(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR))), 9) = "AVAILABLE" Then
        lineType = "AVAILABLE"
        netName = ReturnLastWord(Sheet1.Range("netLIBOR").Offset(yG, xR))
        edgeName = Application.WorksheetFunction.VLookup(netName, Sheet3.Range("tokenTable"), 2, 0)
        netName = "Available " & netName
        edgeName = "Available " & edgeName
        GoTo getTestEdgeRow
    End If
    
    'Determine if it's a Paid, Totals, or Subtotals
    If UCase(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR))) = "TOTAL PAID" Or UCase(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR))) = "TOTALS" Or UCase(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR))) = "SUBTOTALS" Then
        lineType = ""
        yG = yG + 1
        GoTo lineDone
    End If
    
    'Determine if it's a Limit
    If UCase(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR))) = "LIMIT" Then
        limitCount = limitCount + 1
        If limitCount > 1 Then yG = yG + 5
        lineType = "LIMIT"
        netName = Sheet1.Range("netLIBOR").Offset(yG, xR)
        edgeName = netName
        GoTo getTestEdgeRow
    End If
    
    'Determine if it's "Due From" or "Paid From"
    'For now we will not do anything for these options
    If UCase(Left(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR)), 8)) = "DUE FROM" Or UCase(Left(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR)), 9)) = "PAID FROM" Then
        lineType = ""
        yG = yG + 1
        GoTo lineDone
    End If
    
    'Determine if it's a Due or Paid to an actual account.  These appear after the SubTotals line.
    tempNetName = UCase(Trim(Sheet1.Range("netLIBOR").Offset(yG, xR)))
    
    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
                        'must be principal
                        edgeName = "T_" & ReturnFirstWord(netName) & "_Principal" & endOfCode
                    Else...

 

[RoryA]

Without seeing all your code I cannot tell you specifically what is happening within it, but I can say that using Iserror(Application.WorksheetFunction...) does not work. If you use WorksheetFunction, any error is a runtime error, not an error value. (See for example the section titled "Error Handling With Worksheet Functions" on Chip Pearson's site here.) If it is not throwing up a run-time error there are three possibilities:
1. The value searched for is found.
2. You have an On Error statement that is handling the runtime error.
3. Your copy of Excel is unique.

(this sort of categorical statement usually comes back to haunt me, so I wait with bated breath... )

 

[rjn2002]

Thanks for the link to Chip Pearson's site. Question on the "On Error Resume Next": does this statement stay active even after the "err.clear" line? In my mind, this is the only thing that could be causing my "unique version of Excel" to function the way it is, in which case, I apologize for fighting your claim of other error handling statements getting in the way. Thanks again for your help and patience.

 

[RoryA]

Yes it does. On Error Resume Next simply ignores all errors until another error handler comes into play (i.e. another On Error statement), or the sub finishes. An Err.Clear will not change the handler.
However, if the On Error Resume Next is not in this routine, but in the routine that called this one, then as soon as an error occurs in this routine, control will return to the calling routine and execution will resume at its next line, not the next line in this routine. I don't know if that helps to explain what you are seeing?

 

[rjn2002]

It does help. I didn't realize it before, but in the bottom of this sub routine, I have an "On Error Resume Next" statement. Being that I loop through this code multiple times, as long as I hit this statement then looped and eventually hit my IsError functions, then the code works fine. Hence, when I put in the GoTo statements, it skipped these the first loop through the code, thus I hit the Error Handling statement first and then looped.

We can consider this topic closed, and now I can get to sleep at night. Thanks so much for your help.