how to test whether selected variable is within defined range in V

[Abhinandan]

Hi I am new to VBA Programming. So excuse me if the question is a very basic
one.
I am trying to write a macro for to calculate price and stock availability
for a product . Before I look for the product by typing the product number, I
want to test whether the product number is within the defined range. Only
when the product is within the defined range, only then I want to use a
Vlookup finction to calculate the price and stock. If the Product is not
within the range then i want to just get a messgae as product code is not in
the range.
Below is the macro which i wrote. I use Office 2007

Sub test_vlo()

Dim product As String
Dim price As Double
Dim stock As Integer

product = InputBox("Enter the Product Code")
If TypeName(product) = Application.Worksheets(1).Range("a1:j33822").Text
Then
price = Application.WorksheetFunction.vlookup(product, _
Range("a1:j33822"), 5, False)
stock = Application.WorksheetFunction.vlookup(product, _
Range("a1:j33822"), 6, False)
MsgBox product & " price is " & price & " stock is " & stock
Else
MsgBox Application.UserName & " The Product Code does not Exist"
End If

However the problem with this code is that even for the product number
within the defined range, it comes up with an erroe message as The Product
code does not exist.
Can some point me in the right direction

 

[Per Jessen]

Hi

This should do it:

Sub test_vlo()

Dim Product As String
Dim Price As Double
Dim Stock As Integer
Dim f As Variant

Product = InputBox("Enter the Product Code")
Set f = Worksheets(1).Range("a1:j33822").Find(what:=Product)

If Not f Is Nothing Then
Price = f.Offset(0, 4).Value
Stock = f.Offset(0, 5).Value
MsgBox Product & " price is " & Price & " stock is " & Stock
Else
MsgBox Application.UserName & " The Product Code does not Exist"
End If

End Sub

BTW: Do you really want to look in columns A:J to see if Product exists, or
do you have your Products in column A. If the last is the case, then you
should only look in column A, ie. range("A1:A33822")

Regards,
Per

 

[Abhinandan]

Thanks Per,

This suggestion worked and also as you pointed I did modify the range to
colum A only as only column a had products listed

Cheers,
Abhi