Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seaching across the top
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seaching across the top
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 In article , says... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seaching across the top
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 "Tushar Mehta" wrote: 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 In article , says... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seaching across the top
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. -- Regards, Tom Ogilvy "Ben H" wrote in message ... 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 "Tushar Mehta" wrote: 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 In article , says... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seaching across the top
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. "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Ben H" wrote in message ... 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 "Tushar Mehta" wrote: 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 In article , says... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seaching a range | Excel Discussion (Misc queries) | |||
Seaching with no results | Excel Discussion (Misc queries) |