VBA Vlookup

[superfb]

Hi

i am currently learning VBA, and something that i thought should be easy to execute is proving to be a pain.....



Sub TestVlookup()


Dim Product As Variant
Dim Price As Double


Product = InputBox("What code are you looking for?")


Worksheets("Database").Activate


Price = WorksheetFunction.VLookup(Product, Range("Price"), 2, False)


MsgBox ("The cost of the product" & Product & " is" & Price)


Database sheet:

Product	Price
23	1.99
24	25.64
25	13.98
26	11
27	14.79

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

I keep getting a 1004 error......Which is being caused by the vlookup- i have defined the variables and labeles the data sources (name range) as accordingly, but unable to figure out what is going on?

 

[Fluff]

These

Dim Products As Long
Dim Prices As Double

Should both be Variant

Dim Products As Variant
Dim Prices As Variant

 

[superfb]

These

Dim Products As Long
Dim Prices As Double

Should both be Variant

Dim Products As Variant
Dim Prices As Variant

Hi, i did change it to variant, now the error message says "Type Mismatch"...i believe this does that because if i type in a number that is not in the database then it comes with this error message....

 

[Fluff]

If the serach value is not found, you will not get an error.
It sounds more like it has found the value, but the returned value from column 2 is a formulaic error such as #N/A, #NAME, #VALUE etc