Vlookup function

[rotonl]

I'm new to VBA and i'm trying to write a vlookup function to search different sheets when given information and what sheet to look on. the code i have so far is for a command button:

Dim Sheet as String
Dim Price as Double
Dim OD as Double
Price1 = WorksheetFunction.VLookup(OD, Sheets(Sheet).Range("A12:J39"), 10, False)

i seems from looking at other threads that i need to reference an object of somekind and put it after the equals sign and before the rest of the formula. My problem is i have no idea what needs to be referenced. Does and anyone have any ideas?

 

[Mala]

There are some basic errors in the code:

1. You have declared variables Sheet, Price and OD but you have not assigned values to them before the line of code that uses the Vlookup function.

2. 'Sheet' is an Excel object VBA and should not be used as a variable name.

In fact if VLookup is the only thing you need to do, something like the following (without any declarations) would suffice:

OD=Worksheets("SourceSheet").Range("F6").value
Price1 = WorksheetFunction.VLookup(OD, Sheets("TableSheet").Range("A12:J39"), 10, False)

In your actual code, replace 'SourceSheet' with the name of the sheet containing the value of OD and "F6" with the address of the cell that has the value of OD on that sheet.

Similarly replace 'TableSheet' with the name of the sheet containing the Table in which you are looking up.

Hope this helps.