VBA - Syntax for using a variable in a VLOOKUP

[Lidsavr]

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:

    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!

'   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

 

[MrKowz]

Your syntax is correct. The only thing I can think of is if Mnth1 is being pulled in as a serial number for the month instead of the actual string "Oct10". Can you please post the snippet of code where you define Mnth1, and also post the contents of the cell that is populating that variable?

Also, here is some cleaner code that might help ya out:

    Dim x As Variant
    Dim LR As Long
    LR = Worksheets("<").Range("A" & Rows.Count).End(xlUp).Row
    x = Application.WorksheetFunction.VLookup(Mnth1 & "-PN00232-VM1", Worksheets("<").Range("A1:H" & LR), 7, False)
    If IsError(x) Then x = 0
    Worksheets("PN00232 Data").Range("B2").Value = x

 

[Lidsavr]

Mr. Kowz,

Here is my code that defines the variables:

    Dim Mnth1 As String
    Dim Mnth2 As String
    Dim Mnth3 As String
        
    Mnth1 = InputBox("Enter the 1st 3-character MONTH and 2-digit YEAR in the following format: 'MmmYY' (e.g., 'Oct10')")
    Application.Wait (Now + TimeValue("0:00:02"))
    Mnth2 = InputBox("Enter the 2nd 3-character MONTH and 2-digit YEAR in the following format: 'MmmYY' (e.g., 'Nov10')")
    Application.Wait (Now + TimeValue("0:00:02"))
    Mnth3 = InputBox("Enter the 3rd 3-character MONTH and 2-digit YEAR in the following format: 'MmmYY' (e.g., 'Dec10')")

Below is a link to Google Docs where I have posted a .jpg of a portion of the table, including the string that I am looking for in Column A:

https://docs.google.com/leaf?id=0B8ZFmctoHTEBMDA0ZThiMDctZmQ5MS00ZjRjLWI5OTctYjQ3YmRlZTVkNzU1&sort=name&layout=list&num=50
The code (shown in the original message) for the Vlookup is:

'   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(Mnth1 & "-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

Thank you for your help! While I am waiting to hear back, I will try out the cleaner code that you gave me.

Lidsavr

 

[MrKowz]

Try stepping through your code and see what gets returned as x RIGHT AFTER it performs the VLOOKUP.

 

[Smitty]

Just as a note I'd probably use a user form with a combo box for month selection and one for year selection. That way you don't need to worry about a user mis-entering the data, which is all too probable.

 

[Lidsavr]

Mr Kowz,

Try stepping through your code and see what gets returned as x RIGHT AFTER it performs the VLOOKUP.

I stepped through and the return is 0.

I decided on a whim to disable the variable and add the actual string in to perform the Vlookup:

"Oct10-PN00232-VM1"

and it worked perfectly with a return of 249.

That tells me one of two things:

Either the variable is not being stored - or - the variable is input into the vlookup code.

I really want to use the variable. I will make running the report on a weekly basis a breeze!

Is there anything else you can suggest?

Thank you,

Lidsavr

 

[MrKowz]

I think the problem lies in your line of code below. See if that passes through correctly when you are stepping through:

If Err.Number <> 0 Then
        x = 0
    End If

Also, do you still get a 0 value in the code I provided?

 

[Lidsavr]

Smitty,

Just as a note I'd probably use a user form with a combo box for month selection and one for year selection. That way you don't need to worry about a user mis-entering the data, which is all too probable.

Your are absolutely right, but unfortunately in my business (manufacturing), the turn-over rate is high from year to year with the economy being the way it is. Layoffs are happening all the time at many different companies.

I would hate to stick it to my company by writing code that is good for five or ten years and then not be here to help someone figure out how to change the code for the next period of time. Now if the economy turns around in the future... ray:

Thanks for the suggestion (I will keep it on the back burner),

Lidsavr

 

[Smitty]

I would hate to stick it to my company by writing code that is good for five or ten years and then not be here to help someone figure out how to change the code for the next period of time.

That's what code comments are for. And put it this way, if you take a few small steps now that can greatly reduce the error factor in the future then that minimal effort pays off in spades.

 

[ZVI]

Hi, define lookup range outside WorksheetFunction like this:

  Dim Rng As Range
  Set Rng = Worksheets("<").Range("A1:H65536")
  x = Application.WorksheetFunction.VLookup(Mnth1 & "-PN00232-VM1", Rng, 7, False)

Or add .Cells:

  x = Application.WorksheetFunction.VLookup(Mnth1 & "-PN00232-VM1", Worksheets("<").Range("A1:H65536").Cells, 7, False)