Re: Seaching across the top

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 02/16/05 

Date: Tue, 15 Feb 2005 19:15:31 -0500

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" <BenH@discussions.microsoft.com> wrote in message
news:596957C1-EBE1-4EED-8201-93E494DDF9CE@microsoft.com...
> 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 <65D46AC7-FA13-408F-8036-767BEB0E04D1@microsoft.com>,
> > BenH@discussions.microsoft.com 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.
> > >
> >