What is the Best way to "lookup" via VBA

[BIGTONE559]

I originally posted about using vlookup to check in vba. I didn't receive any responses so i figured i was going about it the wrong way. Please share what you feel is the best way to resolve the following scenario:


You have 2 sheets of data.

on sheet 1 row 1:30 you have values in A1:D1
on sheet 2 row 1:30 you have values in A1:D1

you want to look up value D1 (Sheet1) with Value D1 (sheet2) and return B1 (Sheet2) value.

Then we would like to see if that result = to A1 (Sheet1)


I was using something like this:

If Application.WorksheetFunction.VLookup(Range(Cells(x, 4)), Range("checklist"), 2, False) = Cells(x, 2) Then

But i kept receiving range of object global fail.

What would anyone else suggest?

 

[VoG]

Try like this

If Not IsError(Application.VLookup(Range(Cells(x, 4)), Range("checklist"), 2, False)) Then
    If Application.VLookup(Range(Cells(x, 4)), Range("checklist"), 2, False) = Cells(x, 2).Value Then
    'do something
    End If
Else
     'do something else
     
End If

 

[BIGTONE559]

VOG thanks for the reply. .

There must be some error in dealing with the name ranges of workbook. Is there anyway that you can rewrite the code you provided for general ranges?

Everytime i attempted to execute the macro i receive "Range of object global fail"

 

[VoG]

Perhaps you mean

If Not IsError(Application.VLookup(Cells(x, 4).Value, Range("checklist"), 2, False)) Then
    If Application.VLookup(Cells(x, 4).Value, Range("checklist"), 2, False) = Cells(x, 2).Value Then
    'do something
    End If
Else
     'do something else
     
End If

 

[BIGTONE559]

worked perfectly thanks VOG!