Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

Thread: Code needed for Max & Min Values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post Code needed for Max & Min Values

    I have B2:B10 which are dynamic cells & keeps on UPDATING (based on time criteria)
    Corresponding MAXIMUM & MINIMUM values required in E2:E10 & F2:F10
    Code required to accomplish.
    I am using Excel 2007.
    Thanks in adv.
    Sheet1

    B C D E F
    1 MAXIMUM MINIMUM
    2 458.45 458.45 451.23
    3 470.26 470.38 459.22
    4 280.01 285.98 260.01
    5 500 510.05 492.06
    6 9999 10000 9874.2
    7 2222 2255 2220
    8 1111 1181 1111
    9 1090 1090 1090
    10 7755.33 775.33 7755.3

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    How are your values in column B updated?
    Manually? By formula? By link? Copy & Paste?
    How frequently are they being updated?
    Are they all being updated at once, or individually?
    Last edited by Joe4; Sep 9th, 2019 at 11:27 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    Quote Originally Posted by joe4 View Post
    how are your values in column b updated? Real time data
    manually? By formula? By link? Copy & paste? By link
    how frequently are they being updated? Contionously as long as the workbook remains opened

    are they all being updated at once, or individually?
    all gets updated contionously as long as the workbook remains opened

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the resulting VBA window and see if it does what you want:
    Code:
    Private Sub Worksheet_Calculate()
    
        Dim cell As Range
        
        Application.EnableEvents = False
        
    '   Loop through range of values that is being updated
        For Each cell In Range("B2:B10")
    '       Check/update Maximum
            If cell > cell.Offset(0, 3) Then cell.Offset(0, 3) = cell
    '       Check/update Minimum
            If cell < cell.Offset(0, 4) Then cell.Offset(0, 4) = cell
        Next cell
        
        Application.EnableEvents = True
    
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    [QUOTE=Joe4;5339270]Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the resulting VBA window and see if it does what you want:
    I will give correct report by tomorrow Sir

  6. #6
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    Joe4

    Observations found:
    1) Range B2:B10 throws MAXIMUM (HIGHEST) value in E2:E10….that’s fine
    2) BUT it does not throws MINIMUM (LOWEST) value in F2:F10? F2:F10 is Null everytime.
    3) E2:E10 retains values even after the Workbook is re-opened!!! Can E2:E10 (& similarly F2:F10) keep 0 instead (AFTER the workbook is re-opened)?

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    2) BUT it does not throws MINIMUM (LOWEST) value in F2:F10? F2:F10 is Null everytime.
    I tested it out, and it works for me. I wonder if there was a "hiccup" in one of your links, and the connection for some values failed at some point, returning a null, and then it got stuck on that null.
    Does it actually return the word "Null", return a blank, or something else? If we know what it is that is being returned in some cases, we can test and account for it.

    3) E2:E10 retains values even after the Workbook is re-opened!!! Can E2:E10 (& similarly F2:F10) keep 0 instead (AFTER the workbook is re-opened)?
    There were no requirements in the original post on when to reset these values. If that is a requirement, you should add a Workbook_Open script to clear those values out to start (upon opening the file).
    I would caution you not to set them to 0 to start, as that will probably be your MINIMUM value going forward then, unless you have negative values, or unless we update the script to make it not consider 0 to be a possible minimum value.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    [QUOTE=Joe4;5339728]I tested it out, and it works for me...I tested it today also only to find F2:F10 containing only blanks NOT LOWEST value?. I wonder if there was a "hiccup" in one of your links "one of your links".......B2:B10 is Last Traded Price of stocks & ALL B2:B10 gets figures from another worksheet from the SAME Workbook. No sooner than the stocks are traded & the stock prices changes, it makes corresponding B2:B10 change. B2:B10 gets updated as and when the stocks gets traded...so as per me there is NO ISUUES of "hiccup" anytime, and the connection for some values failed at some point, returning a null, and then it got stuck on that null.
    Does it actually return the word "Null", return a blank Return a blank, or something else? If we know what it is that is being returned in some cases, we can test and account for it.

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Code needed for Max & Min Values

    You did not respond to the second issue, regarding resetting of the values when the workbook is re-opened.
    I prefer to make ALL the changes to the code at once, and not piece-meal, so if you answer that question also, I will see if I can make the necessary adjustments for you all at once.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    Dec 2008
    Location
    Mumbai
    Posts
    938
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code needed for Max & Min Values

    Can I make it as A1=1 or 0

    as long as A1=1, B2:B10 should keep on throwing Max and min in E and F as soon as A1=0 it should stop throwing any values in E and F

Some videos you may like

User Tag List

Tags for this Thread

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
  •