VBA - Determine whether there are consecutive zeros in an array

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

Hi Stephen,

Apologies, I sent the above from my mobile and found it a bit fiddley.

I would like to learn how to create a new array, whereby I look through the first column in the array arr, and determine whether or not there are 3 consecutive zeros. If so I would like to put 1 into the array, if not 0. This is where the 0 1 0 comes from - it is what I would like the example data to return.

Hope this is clear

Partially yes. The zeros need to be consecutive for there to be an outputted 1. Additionally, I am unsure (as I am fairly new to coding in general) if adding it as a new row as you suggest will make it easy to analyse. As my next step would be to count the number of 1's to 0's that are outputted.

Stephen,

That is fantastic thank you, as I have said I'm fairly new to this so some of what you have done within this code has developed my knowledge.

I had started to do a flowchart for my code and realised I only really needed to count the zeros in a for loop (as you have done) - so to anyone reading this, it does help if you first plan out code within a flow chart as the problem may actually be simpler than expected!

I have tested it a few times and I can see a few columns in which there are 3+ consecutive zeros however it does not return a 1 in the final row, I shall look into this.

Again, thanks for your help!

EDIT: I think it just required ctr to be reset to 0 after the k loop.

Sub x()


Dim arr As Variant, i As Long, k As Long, m As Long, x, v, ctr As Long


i = 7 'hard-coded rows in array


ReDim arr(1 To i + 1, 1 To 3) 'i+1 to allow for final row tallying zeros


For k = 1 To i
   For m = 1 To UBound(arr, 2)
      x = Rnd()
      If x > 0.5 Then
        arr(k, m) = 0
      Else
        arr(k, m) = 1
      End If
   Next m
Next k


ActiveSheet.UsedRange.ClearContents
'this outputs the array without the last row
Range("A1").Resize(UBound(arr, 1) - 1, UBound(arr, 2)).Value = arr


'this works out last row
For m = 1 To UBound(arr, 2)
    For k = 1 To UBound(arr, 1) - 1
        If arr(k, m) = 0 Then
            ctr = ctr + 1
        Else
            ctr = 0
        End If
        If ctr = 3 Then
            arr(i + 1, m) = 1
            Exit For
        End If
    Next k
    If ctr < 3 Then arr(i + 1, m) = 0
    ctr = 0
Next m


'this outputs the array with the last row
Range("E1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr


End Sub