Results 1 to 4 of 4

Thread: VBA Vlookup & Error Handling problem
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Vlookup & Error Handling problem

    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

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Vlookup & Error Handling problem

    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
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Sep 2010
    Posts
    161
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Vlookup & Error Handling problem

    Thank you Norie it worked like a charm.
    No error message at all.
    Will need to work on my Error handling it seems

  4. #4
    New Member
    Join Date
    Jan 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Vlookup & Error Handling problem

    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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •