I am using Excel 2007.
I am trying to use a variable with vlookup. I have used this code before without the variable and it works great. I am sure I have something wrong with my code, but cannot figure it out. When I run the code, the value returned is 0, which means there is an error. In the case of the code below, when I look up the value manually, it should return: 249.
The table (built from a database) in a worksheet called "<". The value I am looking for is in the 7th column of the table.
I have three variables called Mnth1, Mnth2, Mnth3 that are defined by a msgbox.
My variables are set up as:
For the purpose of this post, Mnth1 = Oct10, Mnth2 = Nov10, and Mnth3 = Dec10.
My Vlookup code is looking for Oct10-PN00232-VM1 and that is found in the first row of the worksheet called: "<"
NOTE: the variable below is in red text to make it easier to find!
Am I using my variable correctly? Do I need to put quotes or other character around the variables? I am not sure how to state this in the code.
Any help is appreciated!
Thanks,
Lidsavr
I am trying to use a variable with vlookup. I have used this code before without the variable and it works great. I am sure I have something wrong with my code, but cannot figure it out. When I run the code, the value returned is 0, which means there is an error. In the case of the code below, when I look up the value manually, it should return: 249.
The table (built from a database) in a worksheet called "<". The value I am looking for is in the 7th column of the table.
I have three variables called Mnth1, Mnth2, Mnth3 that are defined by a msgbox.
My variables are set up as:
Code:
Dim Mnth1 As String
Dim Mnth2 As String
Dim Mnth3 As String
For the purpose of this post, Mnth1 = Oct10, Mnth2 = Nov10, and Mnth3 = Dec10.
My Vlookup code is looking for Oct10-PN00232-VM1 and that is found in the first row of the worksheet called: "<"
NOTE: the variable below is in red text to make it easier to find!
Code:
' Inserts VLookup value for PN00232-VM1 from col 7 into cell B2
Sheets("PN00232 Data").Select
Range("B2").Select
On Error Resume Next ' trap not found error
x = Application.WorksheetFunction.VLookup([COLOR="Red"]Mnth1[/COLOR] & "-PN00232-VM1", _
Worksheets("<").Range("A1:H65536"), 7, False)
If Err.Number <> 0 Then
x = 0
End If
On Error GoTo 0 ' return to normal error messages
Worksheets("PN00232 Data").Range("B2").Value = x
Am I using my variable correctly? Do I need to put quotes or other character around the variables? I am not sure how to state this in the code.
Any help is appreciated!
Thanks,
Lidsavr