+ Reply to Thread
Results 1 to 5 of 5

Loop to Find the Max Value of Data in Multiple Columns

  1. #1
    Forum Contributor
    Join Date
    09-06-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    225

    Post Loop to Find the Max Value of Data in Multiple Columns

    Hello,

    I am trying to create a Loop that will analyze dates in columns D?, E?, F?, G?, and place the most recent date in H?.


    So pretty much there are dates in 4 columns, and I need to find the most recent date for every row in the data set.



    I know I could probably use =MAX(D2, E2, F2, G2), but how could I turn this into a loop, and make it work for the visible cells after the data has been filtered.

    Thanks,

    Nick

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,177

    Re: Loop to Find the Max Value of Data in Multiple Columns

    Hi, Nick,

    maybe something like this for a start (SpecialCclls(xCellTypeVisible) might be used when data is filtered):

    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Loop to Find the Max Value of Data in Multiple Columns

    Another method...

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 12-12-2012 at 03:43 PM.

  4. #4
    Forum Contributor
    Join Date
    09-06-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    225

    Re: Loop to Find the Max Value of Data in Multiple Columns

    HaHoBe,

    That seems to work fine for placing the values in the same worksheet. What if I wanted to place them in a different worksheet? Placing a With Worksheets("WORK").Range("G1") doesnt work.

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,177

    Re: Loop to Find the Max Value of Data in Multiple Columns

    Hi, Nick,

    that line woud always overwrite the data to show only the last entry. If you want every max listed:
    Please Login or Register  to view this content.
    Ciao,
    Holger

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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