hlookup in VBA

[Dennis]

Hi,
is it possible to use HLOOKUP in vba. I'm having problems, which may be
syntax ??

Trying to look across row1 to find which column has contents
"specific_word", then put that column number into a variable.

Any tips welcome.

 

[Tom Ogilvy]

Sub FindWord()
Dim specific_word As String
Dim rng As Range
Dim vVal As Variant
specific_word = "abcd"
Set rng = Range(Cells(1, 10), Cells(2, 30))
vVal = Application.HLookup(specific_word, rng, 2, False)
If IsError(vVal) Then
MsgBox specific_word & " was not found"
Else
MsgBox specific_word & " found, row 2 value " & vVal
End If

End Sub

However, Hlookup doesn't return the location/column where the match was
found. It returns a value in a corresponding row. In the example, I look in
row 1 and return the value below the match in row 2.

You would use MATCH to find the column

Sub FindColumn()
Dim specific_word As String
Dim rng As Range
Dim vVal As Variant
specific_word = "abcd"
Set rng = Range(Cells(1, 10), Cells(1, 30))
vVal = Application.Match(specific_word, rng,0)
If IsError(vVal) Then
MsgBox specific_word & " was not found"
Else
MsgBox specific_word & " found at column " & rng(1,vVal).column
End If

End Sub

 

[Alex@JPCS]

Dennis,
As an alternative to Tom's approach (since I read a lot of ranges into
2-dimension VBA arrays), if you have reason to do that ...

ArrayX=Sheets("TestSheet").Range("TestRange")

gives you a 2D matrix. Then try....

colnum = FindCOLinArray("abcd",1,1,ArrayX)

if colnum<1 then
'Error condition: abcd" not found



Function FindCOLInArray(Target, row, startcol, xArray) as Long
FindCOLInArray = 0
For j = startcol To UBound(xArray, 2)
If xArray(row, j)= Target Then
FindCOLInArray = j
Exit Function
End If
Next j
End Function

(I've skipped all the good variable declaration stuff that Tom has included
for the purpose of brevity. Use HIS coding techniques, not mine.)

Hope this might be helpful,
Alex