VLookup in VBA giving error message

[ayl322]

Hi, I'm having trouble making the vlookup function work in VBA.
the values that are used are all dates, I don't know if that makes a
difference.


Worksheets("sheet1").range("a1").value = application.WorksheetFunction.
_
Vlookup(Range("c1"), range("a:a"), 2)

this gives me a run-time error '1004' Unable to get the vlookup
property of the Worksheet function class


so i tried this:
Worksheets("sheet1").range("a1").value =
application.Vlookup(Range("c1"), _
range("a:a"), 2)

but this gives me a #REF error.

What am I doing wrong?

Any help would be appreciated!

 

[Bob Phillips]

This works for me

Worksheets("sheet1").Range("a1").Value = Application.VLookup _
(Range("c1"), Range("a:b"), 2)


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[George Nicholson]

Vlookup(Range("c1"), range("a:a"), 2)

You are telling Vlookup to return a value from the 2nd column of a 1 column
range. It simply can't do that.

Change a:a to a:b (or something with 2 columns) and see if you get better
results.

HTH,

 

[L. Howard Kittle]

Hi ayl322,

Perhaps you need to rewrite your code similar to this. In your code you are
trying to return a value to A1 and your table array is the same column, A.
Also, you are asking to return the value in the second column of the table
array and you only have one column.

Worksheets("sheet1").Range("A1").Value = _
Application.WorksheetFunction. _
VLookup(Range("F1"), Range("B1:C5"), 2, 0)

So here I want to return a value to A1, using the value in F1 as the lookup
value and the table array is B1:C5 and I want the second column using the 2
and an exact match using the 0.

HTH
Regards,
Howard