Code needed for Max & Min Values

[hsandeep]

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
			MAXIMUM	MINIMUM

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]458.45[/TD]

[TD="align: right"]458.45[/TD]
[TD="align: right"]451.23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]470.26[/TD]

[TD="align: right"]470.38[/TD]
[TD="align: right"]459.22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]280.01[/TD]

[TD="align: right"]285.98[/TD]
[TD="align: right"]260.01[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]510.05[/TD]
[TD="align: right"]492.06[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]9999[/TD]

[TD="align: right"]10000[/TD]
[TD="align: right"]9874.2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]2222[/TD]

[TD="align: right"]2255[/TD]
[TD="align: right"]2220[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]1111[/TD]

[TD="align: right"]1181[/TD]
[TD="align: right"]1111[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]1090[/TD]

[TD="align: right"]1090[/TD]
[TD="align: right"]1090[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]7755.33[/TD]

[TD="align: right"]775.33[/TD]
[TD="align: right"]7755.3[/TD]

</tbody>

 

[Joe4]

Try this version, which I think should address all your issues:

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

 

[hsandeep]

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.

 

[Joe4]

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:

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

 

[hsandeep]

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

 

[hsandeep]

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

 

[hsandeep]

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)

 

[Joe4]

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.

 

[hsandeep]

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

 

[Joe4]

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).

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

 

[hsandeep]

End If[/CODE][/QUOTE]

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