VLookUp.

[Sam]

Hi ...

Yesterday I posted a question about deleting selected columns from a
table of data. I was surprised and grateful to receive several responses
complete with code in only a few hours. I chose to use the macro from Rob
Van Gelder because I could follow it better than the others.

Today I have another question, about VLookUp. I've read the help file
carefully, and it sure seems like this should work:

Sub FLook()
Worksheets(1).Cells(2, 2) = Application.WorksheetFunction _
.VLookup(Worksheets(1).Cells(5, 2), "F5:F16", 1, False)
End Sub

It should check the data in cell 5, 2 (which is really B5) and see if
there's a match in range F5:F16. If there's a match, it should copy it to
cell 2, 2. If not it should put #N/A in cell 2, 2. Get message, `Unable to
get the VLookUp property of the worksheet function class.'

Maybe I don't understand the use of VLookUp??

Sam
--
A man who had lately declared
That property ought to be shared,
Thought it going too far
When they called for his car,
And a list of exceptions prepared.

Thomas Thorneley,
From The Penguin
Book Of Limericks

 

[Rob van Gelder]

Your understanding of VLookup looks ok.

Instead of "F5:F16", try Range("F5:F16")

 

[Tim Zych]

Sub FLook()
With Worksheets(1)
.Cells(2, 2) = Application.VLookup(.Cells(5, 2), .Range("F5:F16"), 1,
False)
End With
End Sub

 

[mike allen]

as the others stated, you need to let it know it is a range, but you may
also need to let it know which sheet the range is in, like if you have an
entire, seperate sheet devoted to lookup ranges ("lookup sheet"). you can
also name the range(s):
application.vlookup(sheets(1).cells(5,2),sheets("lookup
sheet").range("f5:f16"),1,false)
mike allen

 

[Tim Zych]

..

as the others stated, you need to let it know it is a range, but you may
also need to let it know which sheet the range is in, like if you have an
entire, seperate sheet devoted to lookup ranges ("lookup sheet"). you can
also name the range(s):
application.vlookup(sheets(1).cells(5,2),sheets("lookup
sheet").range("f5:f16"),1,false)
mike allen