VBA VLookup Error

[vbcoder2014]

Hi,
I am trying to understand why this vlookup formula is not working -
So for example if Lookupvalue is the year 2001, then Growth should be 10%
when i run the sub, the value of Growth comes out as 0, and i get a type mismatch error.

Dim LookupValue As Integer
Dim Range1 As Range
Dim Growth As Double

LookupValue = Sheets("Temp").Cells(KeyCell, 3).Value
Set Range1 = Sheets("Temp").Range("J8:K12")
Growth = Application.VLookup(LookupValue, Range1, 2, False)

Year Usage
2001 10%
2002 20%
2003 5%
2004 1%

[pareshj]

Hi,

Please check whats the value of KeyCell variable.

Regards,
Paresh J

[ragulduy]

Where is Range1 defined?

[Speshul]

Application.WorksheetFunction.Vlookup ( lookup_value, table_array, col_index_num, [range_lookup] )

[vbcoder2014]

when i run the sub, the value of "LookupValue" shows up correct. When i hover my mouse over it , it shows 2001.
Dim KeyCell As Integer

[ragulduy]

Ok, I see you edited the post to cover the Range1. Spehsul is correct, you are missing the worksheetfunction reference, vlookup is a member of this, not application.

[pareshj]

Hi,

Please attached the excel file so that it would be easier to rectify the error.

Regards,
Paresh J

[ragulduy]

There is no file attached but:

Please Login or Register  to view this content.

[vbcoder2014]

I tried that earlier and i got this error, so i took out the worksheetfunction part -
Runtime error 1004
Unable to get the vlookup property of the worksheet function class

[ragulduy]

I'd suggest uploading a workbook then, what happens if you try and put the formula on the worksheet, does it result in an error?

[vbcoder2014]

i dont have a file, its a huge model and we are trying to add in a vlookup. i can create a dummy excel to show you what i'm doing

[pareshj]

Hi,

Check the attached file and hope this will help you:

Vlookup.xlsm


Note: Check the value of Growth Variable in Debug Mode.

Regards,
Paresh J

[ragulduy]

Hi, the code in the attached file of post #12 seems to work correctly.

[vbcoder2014]

Thankyou all.
I went into my model, and typed in the years. That apparently fixed the issue.
Now i just need to make sure that when the years are pulled in from the sql server (instead of manually typing), they are pasted in so that vb vlookup can find it.

[pareshj]

Hi,

Please mark this thread as SOLVED if you think you got your solution and click * to add reputation to all those who helped you in this issue.


Regards,
Paresh J