VBA-Vlookup problem 'error 1004'

[eferey]

Private Sub GetRoomPrice_Click()
    Set PriceTable = Range("Prices")
    If RoomNR.Text = "" Then GoTo e
    
    RoomPrice = WorksheetFunction.VLookup(RoomNR.Text, PriceTable, 2)e:
End Sub

when i use this code in the button that is on my form i get the following error..

" Run-time error '1004':

Unable to get the VLookup property of the Worksheetfuntion class

"

but the same row works for me fine in my other form, what seems to be the problem ?

 

[Andrew Poulsom]

The VLOOKUP function returns the #N/A error value if it can't find a match. This is fine on a worksheet, but VBA does not tolerate it and raises a 1004 error. So you need to trap it, eg:

Private Sub GetRoomPrice_Click() 
    Set PriceTable = Range("Prices") 
    If RoomNR.Text = "" Then GoTo e 
    On Error Resume Next
    RoomPrice = WorksheetFunction.VLookup(RoomNR.Text, PriceTable, 2)
    If Err <> 0 Then
        RoomPrice = xlErrNA
    End If
End Sub

 

[Dr Zenith]

Wow!! A 2003 thread helps me in 2011, thanks Andrew

 

[Kosoleld]

Hi guys,

Thought it would be worth a mention just to make this thread even more helpful.

I'm making macros for Germans and for some reason German people, in 2017, still use different separators to the rest of us i.e. decimal COMMA and DOT for thousand separator. They also have SEMI COLON ; in formulas on sheet level instead of using COMMA.

VBA always uses standard separators for both, i.e. decimal POINT (dot) and the thousand separator is COMMA. Formulas use COMMA. If you try to set the value of a cell to a string with the formula for a vlookup, cell.Value(or .formula) = "=VLOOKUP(A;B;C;D)" you will get error 1004. If you write this formula into the cell, you won't get an error. The reason is that you need to set the cell formula or value using commas i.e. ="=VLOOKUP(A,B,D,C)". This will transfer over into the cell as "=VLOOKUP(A;B;C;D)".