I have written some VBA code that randomly generates a 1 or 0 depending on a probability. It then stores these into an array, which is defined by the the number of data points I have (rows) and by the number of iterations the user wants to run through (columns).
I have transposed the data into excel and it looks as I'd expect, e.g.
1 0 1
1 1 1
1 0 1
0 0 1
0 0 1
1 1 0
1 0 1
Unfortunately I am not too familiar with VBA so I am struggling with the next part;
I would like to now consider one column (iteration) and find if there are 3 consecutive zeros. If there are, I would like to create a new array that inputs a 1 when there are 3 consecutive zeros found, and a 0 otherwise. So the above example would look like
0 1 0
This would allow me to do some analysis on the number of 1's to 0's etc.
My code so far is shown below
Private Sub CommandButton1_Click()
Dim Time As Double
Dim arr As Variant
Dim PD As Double
Dim PD1 As Double
Dim NoOfPoints As Integer
Dim y As Integer
Dim i, x, k, m, f As Integer
RPM = RPMInputTextbox
PD = PDInputTextbox
Time = TimeInputTextbox
i = iterInputTextbox
k = 1
m = 1
PD1 = PD
NoOfPoints = Int(Time * (RPM / 60))
ReDim arr(1 To i, 1 To NoOfPoints)
For k = 1 To i 'Iteration loop
For m = 1 To NoOfPoints 'Track Points Loop
x = Rnd()
If x > PD1 Then y = 0
Else: y = 1
End If
arr(k, m) = y
Next m
Next k
ActiveSheet.Range("A1:CV8").Value = WorksheetFunction.Transpose(arr)
End Sub
Display More