Link to home
Start Free TrialLog in
Avatar of gazdzid
gazdzidFlag for United States of America

asked on

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

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>
Avatar of gazdzid
gazdzid
Flag of United States of America image

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of dlmille
dlmille
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gazdzid

ASKER

That is awesome thanks!!