VBA-Vlookup problem 'error 1004'

E

eferey

New Member
Joined
May 25, 2003
Messages
15
Code: 
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 ?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
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:

Code: 
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
 
Upvote 0
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)".
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
VBA - For Cycle with "On Error" not working on the second iteration
Replies
9
Views
168
Joe4
Joe4
D
  • Solved
Using VLOOKUp to find a Date in a Data Worksheet
2
Replies
13
Views
239
Alex Blakenburg
A
B
  • Solved
Error on method intersect
Replies
2
Views
122
Beneindias
B
D
  • Solved
Error 1004
2
Replies
18
Views
399
dmt32
D
S
  • Solved
Run-time error '1004'
Replies
3
Views
653
Domenic
D

Forum statistics

Threads
1,224,612
Messages
6,179,890
Members
452,948
Latest member
Dupuhini

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top