+ Reply to Thread
Results 1 to 5 of 5

Seaching across the top

  1. #1
    Ben H
    Guest

    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. #2
    Tushar Mehta
    Guest

    Re: 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 <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.
    >


  3. #3
    Ben H
    Guest

    Re: 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 <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.
    > >

    >


  4. #4
    Tom Ogilvy
    Guest

    Re: 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" <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.
    > > >

    > >




  5. #5
    Ben H
    Guest

    Re: 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" <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.
    > > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1