MAXIMUM & MINIMUM using vba

[hsandeep]

I am using below code to get MAXIMUM & MINIMUM values



from C2:C42 in F2:F42 (for MAXIMUM) & G2:G42 (for MINIMUM) when A1=1

from K2:K42 in N2:N42 (for MAXIMUM) & O2:O42 (for MINIMUM) when I1=1

from S2:S42 in V2:V42 (for MAXIMUM) & W2:W42 (for MINIMUM) when Q1=1

from AA2:AA42 in AD2:AD42 (for MAXIMUM) & AE2:AE42 (for MINIMUM) when Y1=1

Private Sub Worksheet_Calculate()
    If Range("A1").Value <> 1 And Range("I1").Value <> 1 And Range("Q1").Value <> 1 And Range("Y1").Value <> 1 Then Exit Sub
    Dim i As Long
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    
    If Range("A1").Value = 1 Then
        For i = 2 To 42
        If Cells(i, "C").Value <> 0 Then
             Cells(i, "F").Value = Application.Max(Cells(i, "F").Value, Cells(i, "C").Value)
             If Cells(i, "G").Value = 0 Then
                 Cells(i, "G").Value = Cells(i, "C").Value
             Else
                Cells(i, "G").Value = Application.Min(Cells(i, "G").Value, Cells(i, "C").Value)
            End If
            End If
        Next i
    End If
    If Range("I1").Value = 1 Then
        For i = 2 To 42
        If Cells(i, "K").Value <> 0 Then
             Cells(i, "N").Value = Application.Max(Cells(i, "N").Value, Cells(i, "K").Value)
             If Cells(i, "O").Value = 0 Then
                 Cells(i, "O").Value = Cells(i, "K").Value
             Else
                Cells(i, "O").Value = Application.Min(Cells(i, "O").Value, Cells(i, "K").Value)
            End If
            End If
         Next i
    End If
    If Range("Q1").Value = 1 Then
        For i = 2 To 42
        If Cells(i, "S").Value <> 0 Then
             Cells(i, "V").Value = Application.Max(Cells(i, "V").Value, Cells(i, "S").Value)
             If Cells(i, "W").Value = 0 Then
                 Cells(i, "W").Value = Cells(i, "S").Value
             Else
                Cells(i, "W").Value = Application.Min(Cells(i, "W").Value, Cells(i, "S").Value)
            End If
            End If
        Next i
    End If
    If Range("Y1").Value = 1 Then
        For i = 2 To 42
        If Cells(i, "AA").Value <> 0 Then
             Cells(i, "AD").Value = Application.Max(Cells(i, "AD").Value, Cells(i, "AA").Value)
             If Cells(i, "AE").Value = 0 Then
                 Cells(i, "AE").Value = Cells(i, "AA").Value
             Else
                Cells(i, "AE").Value = Application.Min(Cells(i, "AE").Value, Cells(i, "AA").Value)
            End If
            End If
         Next i
    End If
    
    
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    
End Sub

The problem is the code gives the result but makes the workbook ‘hanged’ (Excel is calculating/processing fiercely).



Can someone please help to modify the code OR provide a new code altogether so that the above problem is solved.

 

[NdNoviceHlp]

Why do you have this in a calculation event instead of a sub or function.... it will be recursive. Also, it would be better if you were specific with what sheet your referencing for example....

With Sheets("Sheet1")
 If .Range("A1").Value = 1 Then
        For i = 2 To 42
        If .Cells(i, "C").Value <> 0 Then
             .Cells(i, "F").Value = WorksheetFunction.Max(.Cells(i, "F").Value, .Cells(i, "C").Value)
             If .Cells(i, "G").Value = 0 Then
                 .Cells(i, "G").Value = .Cells(i, "C").Value
             Else
                .Cells(i, "G").Value = WorksheetFunction.Min(.Cells(i, "G").Value, .Cells(i, "C").Value)
            End If
            End If
        Next i
    End If
End With

HTH. Dave

 

[Joe4]

it will be recursive.

Actually, it shouldn't be, because they are disabling events at the beginning of the code.

However, you ask a good question. It may be overkill to have it running on the "Worksheet_Calculate" event, which means anytime any value changes, or any calculation is done on the sheet, this is going to run.

Hansdeep, is that really what you want?
Or do you just want it to run when certain values are updated, which then would probably be better to do in a Worksheet_Change event instead.

 

[NdNoviceHlp]

Joe4 when you return the calculations to automatic at the end of the code, doesn't that cause a calculation event which would again run the routine? Dave

 

[Fluff]

it would be better if you were specific with what sheet your referencing

Because it's in an event code, it automatically refers to that sheet, so no need to qualify it.

 

[Joe4]

Joe4 when you return the calculations to automatic at the end of the code, doesn't that cause a calculation event which would again run the routine? Dave

If you look closely at how they have written the code, they return the calculations to automatic BEFORE they re-enable events. So that should work out OK.

You can prove that to yourself with this simple test:

Private Sub Worksheet_Calculate()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   
    Range("A1") = Range("A1") + 1
   
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True
   
End Sub

Try it out and see what happens. Set up a situation that will fire off a calculation, and watch how A1 only increases by 1 each time and doesn't "call itself".

 

[NdNoviceHlp]

Thanks Joe4 for the learning. Thanks Fluff as well but I was suggesting to move the routine to a sub/function which would then be better to code more specific however that wasn't very clear. I've also come to appreciate the importance of always being as specific as possible with code no matter the circumstance. Thanks again to both of you and I hope that hsandeep has been assisted as well. Have a nice day and stay safe. Dave

 

[Joe4]

Thanks Joe4 for the learning.

No problem! I cannot tell you how many times I replied to help someone, and ended learning something new in the process.
Its one of the cool things about this board!

 

[Fluff]

Agreed

 

[hsandeep]

It may be overkill to have it running on the "Worksheet_Calculate" event, which means anytime any value changes, or any calculation is done on the sheet, this is going to run.

Hansdeep, is that really what you want? Yes the code should fire immediately if anytime C2:C42 value changes in the worksheet & A1 is found equal to 1 & similar.. I1=1....................But the workbook is practically 'hanged'. What to do?