Code needed for Max & Min Values

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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

BCDEF
1 MAXIMUMMINIMUM
2458.45 458.45451.23
3470.26 470.38459.22
4280.01 285.98260.01
5500 510.05492.06
69999 100009874.2
72222 22552220
81111 11811111
91090 10901090
107755.33 775.337755.3

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 73px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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:

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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)?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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.
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,177
Office Version
365
Platform
Windows
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.
 

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
950
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
 

Forum statistics

Threads
1,078,504
Messages
5,340,766
Members
399,394
Latest member
farlow

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top