VBA Vlook up help - Double onerror goto condition

[Mallesh23]

Hello Friends,

I am trying to vlookup on two range,

if Name not found in single range then check in second range,
if not found in there also then I want result not found.
Below is my attempted code.

[TABLE="width: 566"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Score[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Match Score[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]Match Score[/TD]
[/TR]
[TR]
[TD]Sachin[/TD]
[TD="align: right"]105[/TD]
[TD][/TD]
[TD]Sachin[/TD]
[TD]105[/TD]
[TD][/TD]
[TD]Dhoni[/TD]
[TD="align: right"]98[/TD]
[/TR]
[TR]
[TD]Dhoni[/TD]
[TD="align: right"]98[/TD]
[TD][/TD]
[TD]Dhoni[/TD]
[TD]98[/TD]
[TD][/TD]
[TD]Bravo[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]Virat[/TD]
[TD="colspan: 2"]Not Found[/TD]
[TD]Bravo[/TD]
[TD]35[/TD]
[TD][/TD]
[TD]Ponting[/TD]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD]Virender[/TD]
[TD="colspan: 2"]Not Found[/TD]
[TD]Ponting[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]Brett Lee[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Brett Lee[/TD]
[TD="align: right"]20[/TD]
[TD][/TD]
[TD]Brett Lee[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]Sachin[/TD]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD]Bravo[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD]Yuvaraj[/TD]
[TD]111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kapil Dev[/TD]
[TD="colspan: 2"] Not Found[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub Double_Error()
Dim lr As Long, r As Long

Dim myrange1 As Range
Dim myrange2 As Range

Set myrange1 = Sheet1.Range("D:E")
Set myrange2 = Sheet1.Range("G:H")

lr = Sheet1.Range("a500").End(xlUp).Row

Sheet1.Activate

On Error GoTo Error1
For r = 2 To lr
    Cells(r, 2).Value = WorksheetFunction.VLookup(Cells(r, 1), myrange1, 2, False)
Next r
Exit Sub

Error1:
On Error GoTo Error2
    Cells(r, 2).Value = WorksheetFunction.VLookup(Cells(r, 1), myrange2, 2, False)
Resume Next

Error2:
Cells(r, 2).Value = "Not Found"
Resume Next

End Sub

 

[Fluff]

How about

Sub Double_Error()
   Dim Fnd As Range
   Dim Cl As Range
   
   With Sheet1
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         Set Fnd = .Range("D:D").find(Cl.Value, , , xlWhole, , , False, , False)
         If Fnd Is Nothing Then Set Fnd = .Range("G:G").find(Cl.Value, , , xlWhole, , , False, , False)
         If Fnd Is Nothing Then
            Cl.Offset(, 1).Value = "Not Found"
         Else
            Cl.Offset(, 1).Value = Fnd.Offset(, 1).Value
         End If
      Next Cl
   End With

End Sub

 

[Mallesh23]

Hi Fluff,


Fantastic, it has worked for me. Thanks a lot,

Is it possible to get the same answer through array as I have huge data 100000+row .
In future I want to try Vlookup + with array . Thanks in advance !!

Regards,
Mallesh

 

[Fluff]

Glad to help & thanks for the feedback.

I don't often use arrays & not sure how to do that on this occasion.