Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Macro to find Max value on a auto-generated list that change

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have two lists that are auto-generated on a page in Column A and Column B.

    e.g.

    Column A
    Pens
    23
    31
    44
    55
    67

    Column B
    Pencils
    5
    4
    6
    7
    2

    I want to be able to add at the end of the lists (after 67 for Pens and 2 for Pencils) , the Max value for each list using a macro.

    The thing of it is, this list always changes in length so how do I get a marco to automatically choose the range that it needs to sum and add the sum to the end of the lists? How about adding the Sum or Average instead?

    Lastly, I want to add a Conditional Format to the lists, I need the Max Value in the list to be bolded and changed the font color to red. Can this be done?

    Please keep in mind that this list changes every time it generated, so I need to reset the ranges for the Max/Sum/Average and the Conditional Formatting each time.

    Thanks!

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do the ranges always start in the same cell?

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, The ranges alway start on the same cell.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Have you tried with MAX(A:A) ?
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Cosmos

    Try

    Sub AddMyMax()
    Range("A65336").End(xlUp).Offset(1, 0).Formula = _
    "=Max($A$1:" & Range("A65336").End(xlUp).Address & ")"
    End Sub


    This will add a MAX function to the end of any list in Column "A"



Some videos you may like

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
  •