Increment a range

[rcguymike]

So I've got this code I copied and modified from another post to count consecutive 1's and list them out how many times each consecutive amount occurs.
X groups of 2 consecutive, X groups of 3 consecutive...Etc

if this is my input:

Please Login or Register  to view this content.

I get and output of(the top row I typed and the first column doesn't really matter):

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I need to do this for 217 rows from B2:NB2 to B218:NB218. Any help is appreciated!

Later I will also have to list the largest consecutive group for each row if you have any ideas for that. I'm basically a VBA noob but I have a strong history of programming in many other languages.

P.S. Also how would I clear those cells before I write the numbers...currently if I run it multiple times it keeps summing with the values currently in those cells.

[Bernie Deitrick]

Try it like this:

Please Login or Register  to view this content.

The largest consecutive group will be the rightmost column that is filled in your output with a number of 1 or greater

[rcguymike]

Thankyou! Another quick question...Hows does excel do it's array indexing? Does it start at 1 and go up or does it start at 0?

At this line:

Please Login or Register  to view this content.

Should the - 1 be there?

Also I have data in column A that I don't want to erase. Rows(DataOutRow).ClearContents
Clears the whole row. No biggie.

[Bernie Deitrick]

Rows and columns start at 1. I used -1 because that keeps the comparison within your range, since the final comparison is from the second to last column with the last column. For example, if you had B2:D2, you can only compare B to C and then C to D - two comparisons for three columns, so your would index 2 to 3 and not 2 to 4.

Arrays may start at 0 or at 1, depending on your declaration, and whether you use an Option Base 1 (or 0) statement at the top or your module.

To keep A filled, change

Rows(DataOutRow).ClearContents

to

Range("B" & DataOutRow).Resize(1,Columns.Count-1).ClearContents

[rcguymike]

Starting with C=2 should take care of that... Lol but I suppose it would look proper if I start at 1 and use - 1. That's what I'm more familiar with anyways...
Thanks again. I'm beginning to like excel.

[Bernie Deitrick]

Ah - I thought your range started in B: "I need to do this for 217 rows from B2:NB2 to B218:NB218"

[rcguymike]

I didn't have enough coffee today...It's comparing element 2 and element one with 1, and on and so forth, all the way to the last element and the last element - 1.
The original:

Please Login or Register  to view this content.

makes sense if the array starts at 1 because then is starts at element 2(C2) and element 1(C2-1=B2) and compares them with 1, and on and so forth, all the way to NB2 and (NB2 - 1=NA2) and compares them to 1.

Then it does that whole process for each row from B2:NB2 to B218:NB218

[Bernie Deitrick]

Really, if you are still having problems, it would be better if you uploaded your workbook. But I think you have a handle on the issue.

[rcguymike]

Really, if you are still having problems, it would be better if you uploaded your workbook. But I think you have a handle on the issue.

It is working, thanks to you. The workbook contains sensitive information so I can't really upload it. I appreciate your help!

[Bernie Deitrick]

It would be easy to convert to values and just upload the sheet with the 1s and 0s (unless they are used to indicate ascii characters for a secret message! )