Run-time erro '1004': Unable to get the Vlookup property of the WorksheetFunction class

[ExcelTasker]

Experimenting with Vlookup function in VBA and not getting a result with the code below. The problem seems to be in the "Sub cboLocation"...pointing to the Vlookup formula itself.


Private Sub cboLocation_AfterUpdate()
Dim LookupLists As Sheets
Dim cboLocation As ComboBox
Dim txtAppID As TextBox
Dim UserVLookup As Workbooks

Me.txtAppID.Value = WorksheetFunction.VLookup(Trim(Me.cboLocation.Value), Workbooks("UserVLookup").Sheets("LookupLists").Range("A1:H13").Value, 3, False)

End Sub


Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cLoc In ws.Range("Vvalue")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc
End Sub

[AlphaFrog]

Workbooks("UserVLookup").Sheets("LookupLists").Range("A1:H13").Value

Is that the name of the workbook including the file extension (as seen in the workbook's title bar) and is the workbook opened?

[ExcelTasker]

That is the name without the file extension and it's the only open workbook... i'll try with the extension

[ExcelTasker]

No luck even with the extension (.xlsm).

[AlphaFrog]

If it's the only workbook, you could omit the workbook reference.

Please Login or Register  to view this content.

[ExcelTasker]

I tried the new code without any reference to the workbook and the same error 1004 shows itself.

[AlphaFrog]

Remove the .Value property from...
Sheets("LookupLists").Range("A1:H13").Value

[ExcelTasker]

I thought that this is it...but it's a no go. Same error.

[AlphaFrog]

Show the code you have now with the changes.

[ExcelTasker]

Private Sub cboLocation_AfterUpdate()
Dim LookupLists As Sheets
Dim cboLocation As ComboBox
Dim txtAppID As TextBox

Me.txtAppID.Value = WorksheetFunction.VLookup(Trim(Me.cboLocation.Value), Sheets("LookupLists").Range("A1:H13"), 3, False)

End Sub


Private Sub UserForm_Initialize()
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cLoc In ws.Range("Vvalue")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc
End Sub

[AlphaFrog]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


Your code works for me. Not sure why the VLookup errors for you. Here's another way to do it.

Please Login or Register  to view this content.

[ExcelTasker]

Thank you very much. I don't understand how the code works yet but it works. You save me from a lot of beginner's headaches.