Can't get HLookup to work in VBA

[DFStoneJr]

I can't get this expression

Application.WorksheetFunction.Hlookup(today(),range("$C$4:$AA$38"),
35, False)

to work in VBA. I keep getting a 1004 message.

So I've been reduced to the very inelegant method of using this
formula

Range("C1").Formula = "=hlookup(today(),$C$4:$AA$38, 35, False)"

to insert the formula in a cell, then using the Range.Value method to
extract the formula's results.

Any help in getting the first expression to work would be greatly
appreciated, as it's been driving me nuts for most of the afternoon.

TIA.

 

[Frank Kabel]

Hi
try:
Application.WorksheetFunction.Hlookup(date,range("$C$4:$AA$38"),
35, False)