HLOOKUP error using named range in VBA

[Inovo]

I have an HLOOKUP function that works in a spread sheet referencing a named range, but I cannot get it to work in a VBA Macro.

In the spread sheet, the function is as follows:

=HLOOKUP(DATEVALUE("12/12/16"),JN.10634, 2, FALSE)

JN.10634 is the named range, and the lookup works, finding the date and returning the value on the row below.

In my vba macro, I am assigning a cell value with the following statement:

.Cells(8, 12).Value = WorksheetFunction.HLookup(DateValue(Date28), fixedName, 2, False)

Date28 is a variable assigned the value 12/12/16

The code is failing on the variable fixedName and giving the error "Unable to get the HLOOKUP property of the WorksheetFunction class".

During debug mode with the cursor on the fixedName variable it shows the following:
fixedName = "JN.10634"

Any ideas why is knows the variable resolves to JN.10634 (which is the name of a named range) but it generates an error?

 

[myall_blues]

It is not because of the named range - it is because (as the error says) HLOOKUP is not available to to WorksheetFunction. You can demonstrate this in the immediate window of the VBA editor by typing

WorksheetFunction.

and then having a look through the resultant list that pops up.

To fix the problem, put the HLOOKUP into the cell as a formula, and then convert it to a value, like so

Option Explicit
Sub test()
    Dim Date28 As String, fixedName As String
    Date28 = "12/12/2016"
    fixedName = "JN.10634"
    With Cells(8, 12)
        .Formula = "=HLookup(DateValue(""" & Date28 & """), " & fixedName & ", 2, False)"
        .Value = .Value
    End With
End Sub

Regards

Murray

 

[Norie]

How exactly have you declared and set fixedName?

PS HLookUp is available via WorksheetFunction.

 

[myall_blues]

"PS HLookUp is available via WorksheetFunction."

Sorry - my mistake.