Run Time Error 9 - VLookup

[Matt McFadden]

Hello,

I am trying to perform a VLookup function within Xcel. I read in a string that the user selects in the UserForms portion. I am able to store that as a string, and write to a cell. However, when i want to look that string up in my data tab (sheet2) to return a specific value, i am getting run time error 9.

I am not sure where i am going wrong.

 

[Anthony47]

Hummm... Are you sure that the active workbook has a worksheet named Sheet2?
Next problem: you lookup in Range("B2:B10") but you look for column 11 in this one-column range, that of course is not there and would throw error 1004

 

[Matt McFadden]

Hi Anthony!

To address the first part - yes, there is a sheet 2, and i have referenced it in the rest of my code so i would be surprised if it is the reference to it that is the issue...

The second part, in the second argument/condition, i am just setting the range to compare the string to correct? Then it would be column 11 of the same row that comes back as a match compared to the string. At least that is how i understand it.

 

[Anthony47]

No, the "Name" of your worksheet is "Data" (shown in parenthesys in your vbaproject tree), beeing "Sheet2" the "codename"; see Worksheet.CodeName property (Excel)
The Name is what you read on the tab at the bottom of the excel window; the user can change it and you can use it in the form af Sheets("TheName").Range("XX") or Worksheets("TheName").Range("XX")
The CodeName can be changed only using the vba editor (in the properties of the object), and you can use in the form of CodeName.Range("XX"); an additional property of using the CodeName is that it will always refer to the same worksheet regardless which workbook is the active one.

Thus in the VLookup instruction may use Sheet2.Range("XXX:YYY") or Worksheets("Data").Range("XXX:YYY")

If you wish retrieving column 11 of your "vlookuped" range then you have to specify a range with minumum 11 columns; for example Sheets2.Range("B2:L10)
This is the same way you use VLookup function in your formulas:
=VLOOKUP(3,B2:L10,11,FALSE) will return the colum L of your range (or #N/A" if the searched value doesn't exist in column B2:B10)
=VLOOKUP(3,B2:L10,12,FALSE) will return #REF! (or Run time error 1004 if used in a WorksheetFunction instruction)

Bye

 

[Matt McFadden]

Thank you Anthony. I updated the vLookup argument to sheet2.range() and was still getting 1004. For some reason when i updated the range to include the first row "B1", that is what resolved my issues.

 

[Anthony47]

Using Application.WorksheetFunction.VLookup(etc etc) you will get a Run-time-error 1004 if the vlookup fail (the searched element doesn't exist in the range)

You might prefer using

Dim myRet As Variant


myRet = Application.WorksheetFunction.VLookup(Etc etc etc)
If Not IsError(myRet) Then
    A(1) = myRet
Else
    'what to do if vlookup fails?
End If