Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hlookup in VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hlookup in VBA
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
hlookup in VBA
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 "Tom Ogilvy" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hlookup help | Excel Discussion (Misc queries) | |||
HLookup help | Excel Worksheet Functions | |||
Hlookup (sum) | Excel Worksheet Functions | |||
Hlookup? | Excel Worksheet Functions | |||
HLOOKUP and SUM | Excel Discussion (Misc queries) |