v look up in vba

[buddy2000]

I have three rows of data in a range from a2 to c 21 how ever this can change if user adds more data. All I get when I debug is false.



ProductId = InputBox(" Enter product Id", "ProductId")
With Range("A1")
'Number of rows is counted
NproductId = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
'Vlook up function is used to find Description of part
ProductPrice = ProductId
ProductId = .Formula = "=VLOOKUP(sheet1!b1Range(.offset(0,0),.end(xldown)).rows.count,2,false)"
'Vlook up funciont to get part price
ProductPrice = .Formula = "=VLOOKUP(Sheet1!c1,Range(.offset(0,0),.end(xldown)).rows.count,2,false)"
'Print descreption and Price
Debug.Print ProductId, ProductPrice
End With

[mdmackillop]

Simplest is to create a dynamic range name "=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,3)" and refer to this in your code. The example assumes a header in Row 1

[buddy2000]

I am new to VBA I do not understand your answer at all. Yes the data has headers.

[mdmackillop]

Here's a small sample