gazdzid
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>
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(
Sheets("recall").Select
RC = Range("A1").CurrentRegion.
For RCI = 2 To RC
Cells(RCI, 7).Value = Trim(Cells(RCI, 7).Value)
Cells(RCI, 8).Value = Application.vlookup(Cells(
Res = Application.vlookup(Cells(
MsgBox Err & ": " & Error(Err)
Next RCI
ErrorHandler:
MsgBox Err & ": " & Error(Err)
'Resume <or Exit Sub>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is awesome thanks!!
ASKER