I am counting medicine’s NUMBER OF rejections of ‘2 kinds’ in F2:G42 using vba. This I am able to achieve.
Before closing the Excel Workbook (A2 becomes equal to z then), I want to fill blanks again in F2:G42 (using REVISED vba) for next calculations to remain ready.
I am using 3 Worksheets in the SAME Workbook (ws1, ws2 & ws3) with above data & want the same exercise to happen in all the 3 Worksheets.
A2 becomes equal to z in all the 3 Worksheets at the SAME TIME.
I want to get a “REVISED VBA” to fill blanks in a data range (F2:G42) whenever a particular condition is fulfilled (A2=z). The same data range is filled with values when ‘another’ condition is fulfilled (A1=1) & this I am achieving with the help of VBA as given below
[VBA]
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("C2:C42") ' 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 [/VBA]