VBA: Excel crashes at End If

Question

我知道这个函数可以正常工作，但是当我尝试在电子表格中填写时，它会导致 Excel 几乎立即崩溃。当我进入调试模式时，它告诉我 End If 存在问题。我相信这是正确的，因为 End If 结束了前面的 If 语句。

 Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
'Update 20150310
Dim rng As Range
Dim xResult As String
xResult = ""
For Each rng In pWorkRng
    If rng = pValue Then
        xResult = xResult & " " & rng.Offset(0, pIndex - 1)
    End If '<-- crashes here
Next
MYVLOOKUP = xResult
End Function

Answer 1

让我们将循环限制为仅第一列和该工作表上使用的范围：

 Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
'Update 20150310
Dim rng As Range
Dim xResult As String
'resets the range to only the first column and the used range.
'this will limit the cycling to the smallest possible range.
Set pWorkRng = Intersect(pWorkRng.Columns(1), pWorkRng.Parent.UsedRange)
xResult = ""
For Each rng In pWorkRng
    If rng = pValue Then
        xResult = xResult & " " & rng.Offset(0, pIndex - 1)
    End If 
Next
MYVLOOKUP = xResult
End Function