VBA Vlookup & Error Handling problem

flyingmonkeyofdoom

Board Regular
Joined
Sep 22, 2010
Messages
161
The below code is ment to look down the raw data and then use a vba vlookup to find the corrisponding item in a lookup table and bring back the result.
Within the raw data are items that will not be in the lookup table and i was hoping the the "on Error" would catch these; but it seems to work only once then i get the 1004 error box message appear (which this was meant to stop).

would anyone be able to advise me where i've went wrong in my code and how i can fix it?

Code:
Sub AddWaiting()Dim iLastrow As Long, i As Long


On Error GoTo MyErrorHandler:


'Find last row
RawData.Activate
With RawData
    iLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With


'Loop for vlook up
For i = 2 To iLastrow
    If RawData.Range("j" & i) = "" Then
        RawData.Range("o" & i) = ""
        Else
    
        RawData.Range("o" & i) = Application.WorksheetFunction.VLookup(RawData.Range("j" & i), Weighting.Range("A:B"), 2, True)
    End If
Debug.Print i


MyErrorHandler:
  If Err.Number = 1004 Then
    Range("o" & i) = "No Code"
  End If
  
Next i










End Sub
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Try this.
Code:
Sub AddWaiting()
Dim iLastrow As Long, i As Long
Dim Res As Variant

    With RawData
        iLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

        'Loop for vlook up
        For i = 2 To iLastrow
            If .Range("j" & i) = "" Then
                .Range("o" & i) = ""
            Else
                Res = Application.VLookup(.Range("j" & i), Weighting.Range("A:B"), 2, True)
                If IsError(Res) Then
                    .Range("o" & i) = "No Code"
                Else
                    .Range("o" & i) = Res
                End If
            End If
            Debug.Print i

        Next i
    End With
    
End Sub
 
Upvote 0
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:
[/FONT][/COLOR]Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#242729][FONT=Arial]... [/FONT][/COLOR][COLOR=#242729][FONT=Arial]'some other working code[/FONT][/COLOR][COLOR=#242729][FONT=Arial]
[/FONT][/COLOR]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
[COLOR=#242729][FONT=Arial]... 'some other working code
[/FONT][/COLOR]End Sub[COLOR=#242729][FONT=Arial]
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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