Code Skipping Over If and Immediately Going to Else-CodePudding

Prior to adding some error handling my code was working as expected, detecting row indexes and then seeing if row values repeated. However, when I added a line to throw a message box if the column B (Parameter) and column C (RoutingStep) row combinations did not exist, it is now skipping over everything in the If statement and going straight to the Else MsgBox "Row combination " & parametername & " and " & routingname & " cannot be found. Check before running again.", vbCritical line. When debugging, I know that parametername and routingname are set to the correct values. If I remove the Else portion of the code it works fine.

Here is my code:

Function getrowindex(WDnum As String, parametername As String, routingname As String, Optional partialFirst As Boolean = False, Optional partialSecond As Boolean = False)
Dim ws As Worksheet, rowname As Range, addr As String, copy As Long, Output As Integer, rngParam As Range, rngRouting As Range

Set ws = ThisWorkbook.Worksheets(WDnum)

Set rowname = ws.Columns(Parameter).Find(What:=parametername, Lookat:=IIf(partialFirst, xlPart, xlWhole), LookIn:=xlFormulas, MatchCase:=True) 

If Not rowname Is Nothing Then 
    addr = rowname.Address 
    If partialSecond Then routingname = "*" & routingname & "*"
    Do
        If rowname.EntireRow.Columns(RoutingStep).Value Like routingname Then 
            If rngParam Is Nothing Then 
                Set rngParam = ws.Range(rowname, ws.Cells(Rows.Count, Parameter))
                Set rngRouting = rngParam.EntireRow.Columns(RoutingStep)
                If Application.WorksheetFunction.CountIfs(rngParam, parametername, rngRouting, routingname) > 1 Then 
                    MsgBox "This row combination of " & parametername & " and " & routingname & _
                           " appears in multiple lines. Check before running again.", vbCritical 
                    Stop
                End If
            End If
            
            getrowindex = rowname.Row 
            Exit Do
   '##### the line below is where the code is immediately jumping to when calling this function     
   Else
        MsgBox "Row combination " & parametername & " and " & routingname & " cannot be found. Check before running again.", vbCritical
        Stop
        End If
        
        Set rowname = ws.Columns(Parameter).FindNext(After:=rowname)
    Loop While rowname.Address <> addr 
Else 
    MsgBox parametername & " row could not be found. Check before running again.", vbCritical
    Stop
End If

End Function

CodePudding user response：

Like this:

Function getrowindex(WDnum As String, parametername As String, routingname As String, _
                     Optional partialFirst As Boolean = False, _
                     Optional partialSecond As Boolean = False) As Long
                     
    Dim ws As Worksheet, rowname As Range, addr As String
    Dim copy As Long, Output As Integer, rngParam As Range, rngRouting As Range

    Set ws = ThisWorkbook.Worksheets(WDnum)

    Set rowname = ws.Columns(Parameter).Find(What:=parametername, _
                             Lookat:=IIf(partialFirst, xlPart, xlWhole), _
                             LookIn:=xlFormulas, MatchCase:=True)

    If Not rowname Is Nothing Then
        addr = rowname.Address
        If partialSecond Then routingname = "*" & routingname & "*" 'partial match?
        Do
            If rowname.EntireRow.Columns(RoutingStep).Value Like routingname Then
                'got a match - check if more matches exist...
                If rngParam Is Nothing Then
                    Set rngParam = ws.Range(rowname, ws.Cells(Rows.Count, Parameter))
                    Set rngRouting = rngParam.EntireRow.Columns(RoutingStep)
                    If Application.WorksheetFunction.CountIfs(rngParam, parametername, rngRouting, routingname) > 1 Then
                        MsgBox "This row combination of '" & parametername & "' and '" & routingname & _
                               "' appears in multiple lines. Check before running again.", vbCritical
                        Stop
                    End If
                End If
                getrowindex = rowname.Row 'got a single match...
                Exit Do
            End If 'match on second parameter
            
            Set rowname = ws.Columns(Parameter).FindNext(After:=rowname)
        Loop While rowname.Address <> addr
            
        If getrowindex = 0 Then 'was any row match made?
            MsgBox "Row combination '" & parametername & "' and '" & routingname & _
                   "' cannot be found. Check before running again.", vbCritical
            Stop
        End If
    
    Else 'no match on first parameter
        MsgBox parametername & " row could not be found. Check before running again.", vbCritical
        Stop
    End If

End Function