VLookUp.
 

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

VLookUp.
 

Your understanding of VLookup looks ok.

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


"Sam" wrote in message
link.net...
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

VLookUp.
 

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


"Sam" wrote in message
link.net...
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

VLookUp.
 

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("l ookup
sheet").range("f5:f16"),1,false)
mike allen

"Sam" wrote in message
link.net...
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

VLookUp.
 

..

"mike allen" wrote in message
...
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("l ookup
sheet").range("f5:f16"),1,false)
mike allen

"Sam" wrote in message
link.net...
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