Generate a calculation event and count the occurred number

[kuldeepjainesl]

i have a sheet designed for giving a result which is available in M6. Each time the sheet gets calculated it gives either 0 or 1 in M6. the objective it is run this calculation for 50000 times and count how many time it resulted in 1. workbook attached

 

[NARAYANK991]

Hi ,

See the attached file.

I have changed the calculation mode to Manual , with an iteration value of 1.

There are two formulae , in C1 and C2 , which will keep track of the number of iterations and the number of times M6 becomes 1.

You will need to run the macro named cal.

50,000 is a large number , and you might have to wait for hours for it to complete.

Narayan

 

[kuldeepjainesl]

In the mean time i found a solution which even retain the history of experiments by using this code found

Private Sub Worksheet_Calculate()
'Updateby Extendoffice
    Dim Xrg As Range
    Set Xrg = Range("M6:M6")
    If Not Intersect(Xrg, Range("M6:M6")) Is Nothing Then
    capture_cell_value
    End If
End Sub

Sub capture_cell_value()

Dim val As Variant

Sheets("Sheet1").Select

val = Range("M6:M6").Value

Sheets("Sheet1").Select

lRow = Cells(Rows.Count, 1).End(xlUp).Row

Cells(lRow + 1, 1).Value = val

End Sub

Can we add a loop to run this for desired number of time. i tried it somehow it is behaving strange. without even loop it is running for 50 time on each press for F9