Search rows
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Search rows

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a sheet with in column A1:A5000 text.
    In columns D1:J5000 numbers.
    Some rows in the range D1:J5000 have numbers greater then 100
    Is it possible to make a macro that with the ENTER-button jumps to the cell in column A of the row who have a number greater then 100.ENTER-button jumps to the second ceel in columnA of the row who have a number greater then 100 en so on.
    I want a macro to easely show me all the rows who have a number greater then 100 in the range D1:J5000
    Thanks.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    Have you considered Conditional Formatting instead of VBA?

    Adam

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this:

    In column K enter this formula to check if contents of cells D through J is greater than 100.
    =IF(OR(D2>100,E2>100,F2>100,G2>100,H2>100,I2>100,J2>100),"Y","N")

    Then use filters to display only the rows with "Y" in column K.

    To setup the filter highlight the first row in your worksheet then choose Data > Filter > Auto Filter from the menu.


    It's never too late to learn something new.

    Ricky

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Good call, Ricky - I like your idea better than mine.

    I'd use this formula though:

    =IF(MAX(D2:J2)>100,"y","n")

    Cheers!
    Adam

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com