VLookUp.

S

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
 
R

Rob van Gelder

Your understanding of VLookup looks ok.

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

Tim Zych

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

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
 
T

Tim Zych

..

mike allen said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top