Code needed for Max & Min Values

hsandeep

Well-known Member
Joined
Dec 6, 2008
Messages
1,213
Office Version
  1. 2010
Platform
  1. Windows
  2. Mobile
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>
 
Try this version, which I think should address all your issues:
Code:
Private Sub Worksheet_Calculate()

    Dim cell As Range
    
'   Exit if A1 not equal to 1
    If Range("A1") <> 1 Then Exit Sub
    
    Application.EnableEvents = False
    
'   Loop through range of values that is being updated
    For Each cell In Range("B2:B10")
'       Check/update Maximum
        If IsNumeric(cell.Offset(0, 3)) Then
            If cell > cell.Offset(0, 3) Then cell.Offset(0, 3) = cell
        Else
            cell.Offset(0, 3) = cell
        End If
'       Check/update Minimum
        If IsNumeric(cell.Offset(0, 4)) Then
            If cell < cell.Offset(0, 4) Then cell.Offset(0, 4) = cell
        Else
            cell.Offset(0, 4) = cell
        End If
    Next cell
    
    Application.EnableEvents = True

End Sub
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Try this version, which I think should address all your issues:

Joe,
First please allow me to say thanks to you for your time spared for my work.
The last code DID NOT WORK.
By default A1=0, & when A1 became 1

  1. B2:B10 was updating
  2. But E2:E10 retained “older” values that is previous day’s values
  3. F2:F10 DID NOT got lowest values (from B2:B10). It kept on showing blanks

As per me:
1 reason for E2:E10 not getting updated with highest values “could’ be: E2:E10 (when A1=0) contained previous day’s ‘higher’ values than the ones which were generated on current day in B2:B10 (when A1=1)

I am stuck here.

Astonishingly, why your code did not threw LOWEST in F2:F10 when A1=1. It kept on showing blanks

Please help.
 
Upvote 0
First please allow me to say thanks to you for your time spared for my work.
The last code DID NOT WORK.
By default A1=0, & when A1 became 1


  1. B2:B10 was updating
  2. But E2:E10 retained “older” values that is previous day’s values
  3. F2:F10 DID NOT got lowest values (from B2:B10). It kept on showing blanks
Per you description here:
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
I read it to be as follows:
1. If A1 is 1, columns E and F should continue to update
2. If A1 is 0, columns E and F should stop updating, and be frozen to whatever values they are

So, there is nothing resetting or clearing the values. A1 just dictates whether the values in E and F should continue to update, or be frozen to what they currently are.
If you want to reset them somehow by code, you need to tell me how you want that to work.

See if those code update fixes your blank issue:
Code:
Private Sub Worksheet_Calculate()

    Dim cell As Range
    
'   Exit if A1 not equal to 1
    If Range("A1") <> 1 Then Exit Sub
    
    Application.EnableEvents = False
    
'   Loop through range of values that is being updated
    For Each cell In Range("B2:B10")
'       Check/update Maximum
        If (Len(cell.Offset(0, 3)) > 0) And (IsNumeric(cell.Offset(0, 3))) Then
            If cell > cell.Offset(0, 3) Then cell.Offset(0, 3) = cell
        Else
            cell.Offset(0, 3) = cell
        End If
'       Check/update Minimum
        If (Len(cell.Offset(0, 4)) > 0) And (IsNumeric(cell.Offset(0, 4))) Then
            If cell < cell.Offset(0, 4) Then cell.Offset(0, 4) = cell
        Else
            cell.Offset(0, 4) = cell
        End If
    Next cell
    
    Application.EnableEvents = True

End Sub
 
Upvote 0
I read it to be as follows:
1. If A1 is 1, columns E and F should continue to update YES YOU HAVE CORRECTLY UNDERSTOOD
2. If A1 is 0, columns E and F should stop updating, and be frozen to whatever values they are YES YOU HAVE CORRECTLY UNDERSTOOD

By default A1=0 & becomes 1 for a certain time period (30 minutes) during a time range
 
Upvote 0
See if those code update fixes your blank issue:

Is this code running in your trial Excel file? It may be that I am making some 'unknown' mistake/(s).
Can you please upload / send 1 excel file with this code. It would certainly help me Joe4
 
Upvote 0
If you want to reset them somehow by code, you need to tell me how you want that to work.

Best would be:
IF A2=z, 'put blanks in ALL cells of Highest (E2:E10) & Lowest columns (F2:F10) & STOP further ELSE
CONTINUE with IF A1=1, start throwing Highest & Lowest in respective cells of the destination columns (& as per before criteria's)
 
Upvote 0
IF A2=z, 'put blanks in ALL cells of Highest (E2:E10) & Lowest columns (F2:F10) & STOP further ELSE
At this point, hopefully you should be able to start figuring some of this stuff out on your own (one of the reasons I add documentation is so that you can/see understand what I am doing, and hopefully learn some of it). Don't afraid to try it out for yourself - that is how you will learn!

Try adding another block like I just did for A1 at the top of the previous code, to check for the other condition (put it before the A1 check so that it happens first). You will want this one to be an IF...THEN block (not a single line IF...THEN), as you will want it to first clear out the range E2:F10 before exiting the sub (so you will have two steps that happen if the condition is met).

Try it out for yourself, and post back here with the your code attempt if you cannot get it to work.
 
Upvote 0
Try it out for yourself, and post back here with the your code attempt if you cannot get it to work.[/QUOTE]

Joe4

VBA is very difficult for me still I tried ...please check

Private Sub Worksheet_Calculate()


Dim cell As Range

' Exit if A2 not equal to z
If Range("A2") = "z" Then
Range("E2:F10").Select
Selection.ClearContents
Exit Sub


Dim cell As Range

' Exit if A1 not equal to 1
If Range("A1") <> 1 Then Exit Sub

Application.EnableEvents = False

' Loop through range of values that is being updated
For Each cell In Range("B2:B10")
' Check/update Maximum
If (Len(cell.Offset(0, 3)) > 0) And (IsNumeric(cell.Offset(0, 3))) Then
If cell > cell.Offset(0, 3) Then cell.Offset(0, 3) = cell
Else
cell.Offset(0, 3) = cell
End If
' Check/update Minimum
If (Len(cell.Offset(0, 4)) > 0) And (IsNumeric(cell.Offset(0, 4))) Then
If cell < cell.Offset(0, 4) Then cell.Offset(0, 4) = cell
Else
cell.Offset(0, 4) = cell
End If
Next cell

Application.EnableEvents = True


End Sub
 
Upvote 0
VBA is very difficult for me still I tried ...please check
Understood, but this is the best way to learn!

You were actually very close. You are just missing the "End If" statement, that is all.
You can also combine the one line that ends in "Select" and the next that begins "Selection" (it usually is not necessary to select the ranges to work with them; the Macro Recorder is just very literal and records every action).
Code:
' Exit if A2 not equal to z
If Range("A2") = "z" Then
    Range("E2:F10").ClearContents
    Exit Sub
End If
 
Last edited:
Upvote 0
End If[/CODE][/QUOTE]

The code works EXCEPT when A2="z" then it shows Run-time error '404'
Comments please
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top