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

 

[Lidsavr]

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?

No, I get a runtime error with the code you provided. The runtime error says:

run-time error '1004'
Unable to get the Vlookup property of the WorkshopFunction class.


I believe I am getting a 0 (because the code says to give a 0 if there is an error).

On a whim after testing your above suggestion, I decided to test my variables and see if they are being held in memory, so I inserted code (near the top of the code) to see if it would record them on another worksheet. This is the code I used:

    Sheets("2229 Data").Select      'A blank worksheet
    Range("A1").Select
    ActiveCell.Formula = Mnth1
    Range("A2").Select
    ActiveCell.Formula = Mnth2
    Range("A3").Select
    ActiveCell.Formula = Mnth3

There wasn't anything recorded in those cells. I think there is a problem with the way I am defining or gathering the variable data. I have to figure out a way of getting the information into the variable. Is my input box acceptable for holding a variable?

Thanks,

Lidsavr

 

[Lidsavr]

Smitty,

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.

Of course! I wasn't even thinking about the code comments (I do use them!). I will take your suggestion and build a combo box. I do not want to offend Mr. Kowz, but would you look at my last post and see what I found about the variables not being held in memory? Do you think that if the variable data is entered in through a combo box, the variables will hold in memory any better?

I appreciate your help along with the continuing help of Mr. Kowz

Thanks,

Lidsavr

 

[ZVI]

This works for me as well:

Sub test1()
  Dim Mnth1 As String, x
  Mnth1 = "Oct10"
  x = WorksheetFunction.VLookup(Mnth1 & "-PN00232-VM1", Worksheets("<").Range("A1:H65536"), 7, False)
  MsgBox "x = " & x
End Sub

Excel Workbook
	A	B	C	D	E	F	G	H
1	Oct10-PN00232-F11	PN00232	First Pass	FI1	121	54	175	69.14
2	Oct10-PN00232-VM1	PN00232	First Pass	VM1	0	249	249	0
3	Oct10-PN00232-VM2	PN00232	First Pass	VM2	0	236	236	0
Sheet

 

[Jonmo1]

Lidsavr,

Perhaps it's time you post your WHOLE code, instead of a bit here and a bit there.
Even if you think it's too long.
Highlight the parts you think are relevant in Red, but show everything.

 

[Smitty]

Do you think that if the variable data is entered in through a combo box, the variables will hold in memory any better?

No more, no less. The biggest place it will have an impact is preventing input errors.

 

[MrKowz]

Smitty,

I do not want to offend Mr. Kowz, but would you look at my last post and see what I found about the variables not being held in memory? Do you think that if the variable data is entered in through a combo box, the variables will hold in memory any better?

I appreciate your help along with the continuing help of Mr. Kowz

Thanks,

Lidsavr

We are all friends here, and often the best solutions found are when we combine ideas! I welcome other opinions and views

And as Jonmo suggests, perhaps the entire picture needs to be seen.