CODEWITHSUNDEEP
vbaexcel
Sean Doran sdoran35
Sean Doran sdoran35

Reputation: 13

VBA: Excel crashes at End If

I know that this function works correctly, however when I attempt to fill-down in my spreadsheet it causes Excel to crash almost immediately. When I go into debug mode it tells me there is an issue at the End If. I believe this is correct, as the End If ends the previous If statement.

 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

Upvotes: 1

Views: 175

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

lets limit the loop to only the first column and the used range on that sheet:

 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

Upvotes: 1

Related Questions