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.

A	B		D	E		G	H
Name	Score		Name	Match Score		Name	Match Score
Sachin	105		Sachin	105		Dhoni	98
Dhoni	98		Dhoni	98		Bravo	35
Virat	Not Found	Bravo	35		Ponting	75
Virender	Not Found	Ponting	75		Brett Lee	20
Brett Lee	20		Brett Lee	20		Sachin	105
Bravo	35		Yuvaraj	111
Kapil Dev	Not Found

<tbody>
</tbody>

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.