Hace ya bastante tiempo descubrí un mini fallito en las funciones BUSCARH y BUSCARV de Excel, y programé mi propia función LOOKUPH en este artículo. Sin embargo, hoy he estado programando unas macros en Excel y necesitaba hacer algo similar, con lo que reutilicé el código original, dándome cuenta que tenía una fallo:
la función estaba limitada al rango A1:Z9, es decir, a columnas y filas de un único caracter.
¡Así que aquí os dejo la nueva función ligeramente modificada y mejorada!
:GUIÑO
'/**************************************************************************
'* Function: LOOKUPH *
'* *
'* Author: Ruben Alvarez *
'* Date: 01/07/2011 *
'* *
'* Description: Search a reference text into any row in a range and *
'* returns the value in the same column in the specified row *
'**************************************************************************/
Public Function LOOKUPH(RefText As String, LookupRange As String, LookupRow As Integer, ReturnRow As Integer) As String
Dim s As Variant
s = Split(LookupRange, ":", , vbTextCompare)
If UBound(s) = 1 Then
start_col_number = CNumber(CStr(s(0)))
final_col_number = CNumber(CStr(s(1)))
start_col_letter = CLetter(CLng(start_col_number))
final_col_letter = CLetter(CLng(final_col_number))
start_row_number = CInt(Right(s(0), Len(s(0)) - Len(start_col_letter)))
final_row_number = CInt(Right(s(1), Len(s(1)) - Len(final_col_letter)))
Else
LOOKUPH = "#ERROR#"
Exit Function
End If
For lookup_col = start_col_number To final_col_number
If Cells(start_row_number + LookupRow - 1, lookup_col).Text = RefText Then
LOOKUPH = Cells(start_row_number + ReturnRow - 1, lookup_col).Value
End If
Next lookup_col
End Function
Public Function CLetter(v As Long) As String
CLetter = Left(Cells(1, v).Address(1, 0), InStr(1, Cells(1, v).Address(1, 0), "$") - 1)
End Function
Public Function CNumber(v As String) As Integer
CNumber = Range(v & "1").Column
End Function
]]>