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?

 

[6StringJazzer]

How is the named range Price defined? It should be the columns containing both Product and Price (i.e., something like =DatabaseSheet!$A:$B)

Also, Product as Variant may be an issue if it is typed as String after the user enters a value, and you are looking up numbers. Try changing Product from Variant to Long, or changing your code to

[COLOR=#333333]Price = WorksheetFunction.VLookup([/COLOR][COLOR=#ff0000]CLng([/COLOR][COLOR=#333333]Product[/COLOR][COLOR=#ff0000])[/COLOR][COLOR=#333333], Range("Price"), 2, False)[/COLOR]

I have not experimented to replicate your issue but those are the thing I would try.

 

[Fluff]

Try

Sub TestVlookup()
Dim Product As Variant
Dim Price As Variant

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

Worksheets("Database").Activate

Price = Application.vlookup(CLng(Product), Range("Price"), 2, False)
If Not IsError(Price) Then
   MsgBox ("The cost of the product" & Product & " is" & Price)
End If
End Sub

It's better to Use Application rather then WorksheetFunction as you can test for an error rather than the code crashing

 

[wwbwb]

Try changing the Product as variant to Integer.

 

[superfb]

How is the named range Price defined? It should be the columns containing both Product and Price (i.e., something like =DatabaseSheet!$A:$B)

Also, Product as Variant may be an issue if it is typed as String after the user enters a value, and you are looking up numbers. Try changing Product from Variant to Long, or changing your code to

[COLOR=#333333]Price = WorksheetFunction.VLookup([/COLOR][COLOR=#ff0000]CLng([/COLOR][COLOR=#333333]Product[/COLOR][COLOR=#ff0000])[/COLOR][COLOR=#333333], Range("Price"), 2, False)[/COLOR]

I have not experimented to replicate your issue but those are the thing I would try.

Price is defined as:

=Database!$A$2:$B$6

I have changed this to =Database!$A:$B

And changed Product from Variant to Long and now it seems to be working! Thank you

What does the function Clng do?

 

[superfb]

Try

Sub TestVlookup()
Dim Product As Variant
Dim Price As Variant

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

Worksheets("Database").Activate

Price = Application.vlookup(CLng(Product), Range("Price"), 2, False)
If Not IsError(Price) Then
   MsgBox ("The cost of the product" & Product & " is" & Price)
End If
End Sub

It's better to Use Application rather then WorksheetFunction as you can test for an error rather than the code crashing

Thank you for your input - however if i type in 5000 i get a run time error?

 

[Fluff]

What is the error message & what line is highlighted when you click debug?

CLng converts a string value to a number.

 

[superfb]

What is the error message & what line is highlighted when you click debug?

CLng converts a string value to a number.

Run time error 13

this appears because i type a huge number like 5000, which isnt in the database....

 

[Fluff]

You shouldn't be getting an error if the value is not in the database.
Have you changed the code I supplied?
Also what line of code gives the error?

 

[superfb]

You shouldn't be getting an error if the value is not in the database.
Have you changed the code I supplied?
Also what line of code gives the error?

Sub TestVlookup()


Dim Products As Long
Dim Prices As Double


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




Worksheets("Database").Activate


Prices = Application.VLookup(CLng(Products), Range("Price"), 2, False)
If Not IsError(Price) Then
MsgBox ("The cost of the product " & Products & " is" & " £" & Prices)
Else
MsgBox ("Not in Database")
End If



i believe if i put in a large number the error arises from the If formula