代码跳过If并立即转到Else

Question

在添加一些错误处理之前，我的代码按预期工作，检测行索引，然后查看行值是否重复。但是，如果列B(Parameter)和列C(RoutingStep)的行组合不存在，当我添加一行来抛出消息框时，它现在跳过If statement中的所有内容，直接转到Else MsgBox "Row combination " & parametername & " and " & routingname & " cannot be found. Check before running again.", vbCritical行。调试时，我知道parametername和routingname设置为正确的值。如果我删除代码的Else部分，它就可以正常工作。

这是我的代码：

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

Answer 1

像这样：

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