VLOOKUP in VB code

[Phillip Topping]

I am having trouble getting the VLOOPUP function to work in a macro. I can
get VLOOKUP to work perfectly in a cell formula but when I try and repeat
this in a macro I keep getting an error message.

If dlgFeesForm.txtEquipHirePeriod.Value < 5 Then

dailyvalue = dlgFeesForm.txtEquipHirePeriod.Value
'dailyrate =
Application.WorksheetFunction.VLookup(dailyvalue, DailyHireRates, 2, False)

End If

My error message appears whenever the macro reaches the line with Vlookup in
it, what am I doing wrong??

And yes I know its a comment at the moment, I have done this so I can test
the remainder of the macro.

TIA
Phillip

 

[Bob Phillips]

Phillips,

Is DailyHireRates a worksheet named range? If so, use
Range("DailyHireRates") in the formula.

 

[Alan Beban]

What is "DailyHireRates"? It would have to be a Range variable for the
code to work. If, for example, it is a range name, then you need
Range("DailyHireRates") instead of DailyHireRates.

Alan Beban

 

[Phillip Topping]

Bob and Alan,
Yes it is a named range and I had tried doing what you both suggested but I
still get the same error message.

Here is the changed code:

If dlgFeesForm.txtEquipHirePeriod.Value < 5 Then

dailyvalue = dlgFeesForm.txtEquipHirePeriod.Value
dailyrate = Application.WorksheetFunction.VLookup(dailyvalue,
Range("DailyHireRates"), 2, False)

End If

Here is the error message I get in the dialogue box:

Run-time error '1004':

Unable to get the Vlookup property of the WorksheetFunction class

Phillip