VBA - VLOOKUP in FOR loop, error handling issue

[LuciakPL]

Hei.

I have this code and can't crack why I get Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class
Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
... 'some other working code
If (Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15) Then
    Application.ScreenUpdating = False
    lastRowM = ActiveSheet.Range("M" & ActiveSheet.Rows.Count).End(xlUp).Row
    lastRowN = ActiveSheet.Range("N" & ActiveSheet.Rows.Count).End(xlUp).Row
    lastRowO = ActiveSheet.Range("O" & ActiveSheet.Rows.Count).End(xlUp).Row
    lastRowLL = WorksheetFunction.Max(lastRowM, lastRowN, lastRowO)
    If lastRowM > 3 And lastRowN > 3 And lastRowO > 3 Then
        For i = 4 To lastRowO
            On Error Resume Next
            ActiveSheet.Range("P" & i) = Application.WorksheetFunction.VLookup(ActiveSheet.Range("O" & i), ActiveSheet.Range("M4:O" & lastRowLL), 2, False)
            On Error GoTo 0
        Next i
    End If
    Application.ScreenUpdating = True
End If
... 'some other working code
End Sub

 

[Dryver14]

Try removing the line, on error goto 0

I do find it odd that you have column O in the lookup table but I suppose if you are only getting column 2 it is not a major problem

 

[LuciakPL]

Thanks but I've tried that already.
Have tried to remove column O from the loop-up, just in case. No difference as well.
Still getting error when there's value in column O which does not exist in look-up array.

 

[Dryver14]

 
If (Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15) Then
    Application.ScreenUpdating = False
    lastRowM = ActiveSheet.Range("M" & ActiveSheet.Rows.Count).End(xlUp).Row
    lastRowN = ActiveSheet.Range("N" & ActiveSheet.Rows.Count).End(xlUp).Row
    lastRowO = ActiveSheet.Range("O" & ActiveSheet.Rows.Count).End(xlUp).Row
    lastRowLL = WorksheetFunction.Max(lastRowM, lastRowN, lastRowO)
    If lastRowM > 3 And lastRowN > 3 And lastRowO > 3 Then
        For i = 4 To lastRowO
            On Error Goto ErrorHandler
            ActiveSheet.Range("P" & i) = Application.WorksheetFunction.VLookup(ActiveSheet.Range("O" & i), ActiveSheet.Range("M4:O" & lastRowLL), 2, False)
            On Error GoTo 0
        Next i
    End If
    Application.ScreenUpdating = True

End If
ErrorHandler: Resume Next
​

Try an errorhandler as above

 

[LuciakPL]

This one gives me Run-time error '20': Resume without error...
Could it be that problem is with doing it in Worksheet_Change Sub?

 

[Dryver14]

put the error handler at the end of the code before end sub

 

[Dryver14]

The other thing to do is take the formula out of a loop and insert directly into the range

 

[LuciakPL]

Still the same with first tip and the second one I can not use as without the loop it's not usable for me.
I went around it with two For loops though. Works as needed.
Thanks for the effort

...
    If lastRowM > 3 And lastRowN > 3 And lastRowO > 3 Then        
        For i = 4 To lastRowO
            For j = 4 To lastRowM
               If ActiveSheet.Range("O" & i) = ActiveSheet.Range("M" & i) Then
                    ActiveSheet.Range("P" & i) = ActiveSheet.Range("N" & i)
                End If
            Next j
        Next i
    End If
...