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




--
Regards,
Tom Ogilvy


Dennis wrote in message
...
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.