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

1. ## Re: Code needed for Max & Min Values

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```

2. ## Re: Code needed for Max & Min Values

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

3. ## Re: Code needed for Max & Min Values

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```

4. ## Re: Code needed for Max & Min Values

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

5. ## Re: Code needed for Max & Min Values

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

6. ## Re: Code needed for Max & Min Values

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)

7. ## Re: Code needed for Max & Min Values

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.

8. ## Re: Code needed for Max & Min Values

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

9. ## Re: Code needed for Max & Min Values

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```

10. ## Re: Code needed for Max & Min Values

End If[/CODE][/QUOTE]

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