VBA - VLOOKUP in FOR loop, error handling issue

LuciakPL

New Member
Joined
Jan 9, 2018
Messages
7
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?

Code:
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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.
 
Upvote 0
Rich (BB 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 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
 
Upvote 0
This one gives me Run-time error '20': Resume without error...
Could it be that problem is with doing it in Worksheet_Change Sub?
 
Last edited:
Upvote 0
The other thing to do is take the formula out of a loop and insert directly into the range
 
Upvote 0
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 :)

Code:
...
    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
...
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top