hsandeep
Well-known Member
- Joined
- Dec 6, 2008
- Messages
- 1,215
- Office Version
- 2010
- Platform
- Windows
- Mobile
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
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.
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
VBA Code:
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.