# 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

• Welcome to Ozgrid. Please remember to use code tags in future as per forum rules.

Where does the 0 1 0 come from? I can see 3 consecutive 0s in the second column.

• 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

• Ok I think I see. Do you mean you want to add a new row into your array which will be 1 if the column above has 3 0s and 0 if not?

• 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.

• Try this. I don't think there's a quick way to check for 3 consecutive zeros so this involves a loop. I have simplified your code a little just to do this bit of it, but I hope you can follow. Sample workbook also attached.

An alternative to adding a last row to the array would be to create a new array of 1 x 3.

## Files

• 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.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!