Seaching across the top

[Guest]

Hi all
I am trying to write a macro that will search row 1 for a text value I
define and then return the column letter. I managed to write one for up-down
and now i need to find one for left-right...

Thanks
Ben H.

 

[Tushar Mehta]

How did you get one for up-down? Did it use VLOOKUP? If not, it
probably should. And, you could use HLOOKUP for left-right.

Or maybe, you used Find. If so, you should be able to tweak it to go
l-r.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Tushar, I really wrote a function that is about 15 lines long that goes in a
loop and compares first the valve of the cell to what i want and quits if
they match. Then it compares the valve agaist an Empty to see if it ran out
of cells and it quits if that true. It keeps running the loop until one of
them is matched. I can't use the VLOOKUP since I have Excel 97. I really
want to modify this one so that I can something like this since I know it
works on Excel 97.

Thanks
Ben

My macro is below

Emergeny_Escape = False ' Control to exit loop when Null is found
Value_Found = False ' Control to exit loop
Row_Value = 1 ' Keeps current row value

' Starting Value retravial loop
Do
What_I_Got = UCase(Sheets(1).Range("A" & Row_Value & "").Value)

If (What_I_Got = UCase(What_I_Want)) Then
Value_Found = True
ElseIf (What_I_Got = Empty) Then
Emergency_Escape = True
Else
Row_Value = Row_Value + 1
End If
Loop While (Value_Found = False) And (Emergency_Escape = False)
' Ending Value retravial loop

 

[Tom Ogilvy]

set rng = Range(cells(1,1),Cells(1,256).End(xltoLeft))
res = Application.Match(sStr,rng,0)
if not res is nothing then
msgbox "Column: " & res
Else
msgbox "Not found"
end if

replacement for yours

set rng = Range(cells(1,1),Cells(rows.count,1).End(xlup))
res = Application.Match(sStr,rng,0)
if not res is nothing then
msgbox "row: " & res
Else
msgbox "Not found"
end if

--
Regards,
Tom Ogilvy



I don't know where you got the idea Vlookup doesn't work in xl97.

 

[Guest]

Tom, I got the idea because I couldn't find any documentation on it in VBA
that came with Excel 97. It kept telling me I had misspelled it or directing
me to the "Find" function.

Thanks again - Ben H.