Seaching across the top

[Ben H]

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

In article <[email protected]>,
[email protected] 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.
>

[Ben H]

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 <[email protected]>,
> [email protected] 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.
> >
>

[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.

--
Regards,
Tom Ogilvy




"Ben H" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> > [email protected] 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.
> > >
> >

[Ben H]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 <[email protected]>,
> > > [email protected] 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.
> > > >
> > >
>
>
>