2 Looping Vlookups Error Handling

[radonwilson]

How to handle the error of 2 looping vlookups which are using the same counter/offsetting variable? I have successfully handled errors for vlookup 1 until my second vlookup1 started running. As soon as my vlookup 2 falls into error it immediately goes to the error label which in results affecting my vlookup 1 , how can I handle errors of both these vlookups separately?

Here is my code, please have a look. I don't want to use On Error Resume Next.

test.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Customer Name	Unit Price
2	Allen Rosenblatt	15.74					VLOOKUP 1				VLOOKUP 2
3	Barry French	216.85				Customer Name	Unit Price			Customer Name	Unit Price
4	Carl Ludwig	20.88				Carl Ludwig	20.88			Edward Hooks	500.98
5	Carlos Soltero	8.4				Carlos Soltero	Not Found			Eugene Barchas 1
6	Claudia Miner	5.28				Claudia Miner 1	Not Found			Jack Garza	40.96
7	Clay Rozendal	195.99				Neola Schneider 1	Not Found			Jim Radford	221.98
8	Don Miller	2.88				Sylvia Foulston	Not Found			Julia West 1
9	Edward Hooks	500.98
10	Eugene Barchas	625.85
11	Jack Garza	40.96
12	Jim Radford	221.98
13	Julia West	95.95
14	Muhammed MacIntyre	38.94
15	Neola Schneider	39.89
16	Sylvia Foulston	201.96
Sheet1

Option Explicit

Sub vlookup()

Dim c As Integer

On Error GoTo Last


For c = 1 To 5

Range("G" & c + 3).Value = Application.WorksheetFunction.vlookup( _
Range("f" & c + 3).Value, Range("a:b"), 2, 0)

Next c


For c = 1 To 5

Range("k" & c + 3).Value = Application.WorksheetFunction.vlookup( _
Range("j" & c + 3).Value, Range("a:b"), 2, 0)

Next c


Exit Sub
Last:
If Err.Number = 1004 Then
Range("G" & c + 3).Value = "Not Found"
Resume Next
End If

End Sub

 

[StephenCrump]

Here's one way you could do it:

Sub MyVlookup()

    With Range("G4:G8")
        .Formula = "=IFERROR(VLOOKUP(F4,A:B,2,),""Not Found"")"
        .Value = .Value
    End With
   
    With Range("K4:K8")
        .Formula = "=IFERROR(VLOOKUP(J4,A:B,2,),""Not Found"")"
        .Value = .Value
    End With
   
End Sub

 

[bebo021999]

Sub test()
For Each cell In Range("F4:F8")
    If Range("A2:A16").Find(cell) Is Nothing Then
        cell.Offset(, 1).Value = "Not Found"
    Else
        cell.Offset(, 1).Value = Range("A2:A16").Find(cell).Offset(, 1)
    End If
Next
    For Each cell In Range("J4:J8")
    If Range("A2:A16").Find(cell) Is Nothing Then
        cell.Offset(, 1).Value = "Not Found"
    Else
        cell.Offset(, 1).Value = Range("A2:A16").Find(cell).Offset(, 1)
    End If
Next
End Sub

 

[Peter_SSs]

Another possibility to try

Sub LookupValues()
  With Range("G4:K" & Columns("F:J").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row)
    Union(.Columns(1), .Columns(5)).FormulaR1C1 = "=if(RC[-1]="""","""",iferror(vlookup(RC[-1],C1:C2,2,0),""Not Found""))"
    .Value = .Value
  End With
End Sub

 

[radonwilson]

Thanks all for your solutions.

But how can I fix my code?

 

[Fluff]

Rather than using general error handling, you should check each lookup like

Sub vlookup()

Dim c As Integer
Dim Ans As Variant


For c = 1 To 5
   Ans = Application.vlookup(Range("f" & c + 3).Value, Range("a:b"), 2, 0)
   If IsError(Ans) Then
      Range("G" & c + 3).Value = "Not Found"
   Else
      Range("G" & c + 3).Value = Ans
   End If
Next c


For c = 1 To 5
   Ans = Application.vlookup(Range("j" & c + 3).Value, Range("a:b"), 2, 0)
   If IsError(Ans) Then
      Range("k" & c + 3).Value = "Not Found"
   Else
      Range("k" & c + 3).Value = Ans
   End If
Next c


End Sub

 

[radonwilson]

Rather than using general error handling, you should check each lookup like

Sub vlookup()

Dim c As Integer
Dim Ans As Variant


For c = 1 To 5
   Ans = Application.vlookup(Range("f" & c + 3).Value, Range("a:b"), 2, 0)
   If IsError(Ans) Then
      Range("G" & c + 3).Value = "Not Found"
   Else
      Range("G" & c + 3).Value = Ans
   End If
Next c


For c = 1 To 5
   Ans = Application.vlookup(Range("j" & c + 3).Value, Range("a:b"), 2, 0)
   If IsError(Ans) Then
      Range("k" & c + 3).Value = "Not Found"
   Else
      Range("k" & c + 3).Value = Ans
   End If
Next c


End Sub

Is there is any difference between the 2 codes given below?

IsError(Application.vlookup(Range("f" & c + 3).Value, Range("a:b"), 2, 0))

IsError(Application.worksheetfuntion.vlookup(Range("f" & c + 3).Value, Range("a:b"), 2, 0))

As you can see that in the first code vlookup is written right after the Application. and if I am writing it in the same way I am not getting errors.

But If I am writing it as the per second code I am getting errors.

So what is the difference between writing these two?

 

[StephenCrump]

If the VLookup fails, Application.Vlookup will return an error value which can be tested using IsError().

But if you're using Application.WorksheetFunction.Vlookup (and spelling WorksheetFunction correctly) and the Vlookup fails, you'll get a runtime error and be prompted to stop the code or go into debug mode.

To use WorksheetFunction, you'd need to wrap like this:

On Error Resume Next
ans = Application.WorksheetFunction.VLookup(Range("f" & C + 3).Value, Range("a:b"), 2, 0)
If Err Then
    MsgBox Err.Number
    'do something?
End If
On Error GoTo 0