Pls Help, Runtime Error using hlookup
 

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 a
xls file.


--
jorgemerizalde
------------------------------------------------------------------------
jorgemerizalde's Profile: http://www.excelforum.com/member.php...o&userid=25890
View this thread: http://www.excelforum.com/showthread...hreadid=266415

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

"jorgemerizalde" wrote:


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 a
xls file.


--
jorgemerizalde
------------------------------------------------------------------------
jorgemerizalde's Profile: http://www.excelforum.com/member.php...o&userid=25890
View this thread: http://www.excelforum.com/showthread...hreadid=266415