Pls Help, Runtime Error using hlookup

[marcus01]

Hello,
I am somewhat new at VBA for excel, I am trying to write some code tha
looks up data in a spreadsheet based on the first column or row. I wan
to use the excel worksheet fuctions, but when I try to use them I get
runtime error that says Run-Time error '1004'
Unable to get the Hlookup property of the WorksheetFunction class
I get this error with other worksheet funcions as well, and I am prett
sure the code is right because I even tried cutting and pasting severa
examples. What am I missing?

Thanks

 

[Bob Umlas]

It would help if you posted the exact code...

Bob Umlas
Excel MVP

 

[jorgemerizalde]

I am getting the exact same error message:

"Run-time error '1004':
Unable to get the HLookup propertyoh the WorksheetFunction class"

when trying to run this code:

Private Sub CBAceptar_Click()
LastCol = Sheets("EI").Range("A1").End(xlToRight).Column
Fecha = CBFechasConsultas.Value
Rango = Sheets("EI").Range(Cells(1, 2), Cells(1, LastCol))
ColFecha = WorksheetFunction.HLookup(Fecha, Rango, 1, False).Column
End Sub

Please help.

P.S. the code is for a command button in a user form I created for
xls file

 

[Guest]

The Hlookup worksheet function returns a value and not a range object so it
does not have a column property. If you are wanting to return the column one
way would be:

Private Sub CBAceptar_Click()

Dim rango As Range
Dim lastCol As Integer
Dim fndCol As Range
Dim ColFecha As Integer
Dim fecha As String

lastCol = Sheets("EI").Range("A1").End(xlToRight).Column
fecha = CBFechasConsultas.Value
Set rango = Sheets("EI").Range(Cells(1, 2), Cells(1, lastCol))

Set fndCol = rango.Find(fecha)
If Not fndCol Is Nothing Then
ColFecha = fndCol.Column
End If

End Sub

Hope this helps
Rowan