VBA V-Lookup stop and end function When value is not found

gazdzid
gazdzid used Ask the Experts™
on
Hello,

I was hoping that the code found below could be altered such that when a value in a V-Lookup is not found the fuction would end with a value passed to the sub.

Found below is my code

Sub format()

  'Call PullData         'Need to develop - Pulls dat from AS400 to each excell sheet
  Call CombinedSheets   'combines enduser reports (mpj, ont, mpj, jz) to Sheet "recall"
        x = VerifyComponents() 'verifys Sheet Recall (Column G to Component (Column G)

            If x <> True Then
                Exit Sub
            End If




   
   
 


End Sub
   
   
 


End Sub
Function VerifyComponents()

Dim x As String
Dim RC As Long
Dim RCI As Long
Dim Res As Variant
 On Error GoTo ErrorHandler
'RCI = 2
Set rng = Sheets("component").Range("B2", "B3000")
Sheets("recall").Select
RC = Range("A1").CurrentRegion.Rows.Count

        For RCI = 2 To RC
                Cells(RCI, 7).Value = Trim(Cells(RCI, 7).Value)
                Cells(RCI, 8).Value = Application.vlookup(Cells(RCI, 7), rng, 1, 0)
                Res = Application.vlookup(Cells(RCI, 7), rng, 1, 0)
               
                 MsgBox Err & ": " & Error(Err)

              Next RCI

ErrorHandler:
 
 MsgBox Err & ": " & Error(Err)

'Resume <or Exit Sub>
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Let me add, my return is "#N/A" when this is received, I would like to notify the user that the component does not exist and abort the macro.
Most Valuable Expert 2012
Top Expert 2012
Commented:
I would write the macro more like this.  You don't need error handler for the vlookup, but you do need to handle the event when the vlookup doens't return a match.  Use of Application.Vlookup into a variant and testing with IsError(variant variable) would be the approach.

Inform your users based on the returned value of FALSE from the function, then exit your sub.

Note on line 32, the exit function happens before any value to the VerifyComponents is changed, its initial value is FALSE, so if exit happens, your result back in the main sub is FALSE.  If it makes it through the whole function, then it is set to true with that statement before the exit function statement.

FYI - as an aside, if you had used WorksheetFunction.Vlookup, you WOULD have to use an error trap.  I believe the Application.Vlookup approach to be the better alternative, but that's my bias.

Here's my take:

Sub format()

    'Call PullData         'Need to develop - Pulls dat from AS400 to each excell sheet
    Call CombinedSheets   'combines enduser reports (mpj, ont, mpj, jz) to Sheet "recall"
    x = VerifyComponents()    'verifys Sheet Recall (Column G to Component (Column G)

    If x <> True Then
        'inform your users here...
        MsgBox "failed.... whatever..."
        Exit Sub
    End If

End Sub

Function VerifyComponents() as Boolean 'starts out as false, until changed to true by the last statement in the function

Dim x As String
Dim RC As Long
Dim RCI As Long
Dim Res As Variant

    'RCI = 2
    Set Rng = Sheets("component").Range("B2", "B3000")
    Sheets("recall").Select
    RC = Range("A1").CurrentRegion.Rows.Count

    For RCI = 2 To RC
    
        Cells(RCI, 7).Value = Trim(Cells(RCI, 7).Value)
        Res = Application.VLookup(Cells(RCI, 7), Rng, 1, 0) 'will not generate an error, but will populate the Res variable such that it can be tested for an error.
        
        If IsError(Res) Then Exit Function 'this is how you test it.
        
        Cells(RCI, 8).Value = Res

    Next RCI

    VerifyComponents = True
End Function

Open in new window


Cheers,

Dave

Author

Commented:
That is awesome thanks!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial