Smart Analysis Macro

[alexaronson]

I have a spread sheet that has item # in column A Customer in columnB starting on row 3 going to row 28,000+. I have data for these Customers that go from Column C to AL (26 rows in all).

What I want to do is develop a macro that will count the number of times I have consecutive zero points repeating for a given range in a row and place the output in AJ of that row.

I need to adjust the evaluation period or range in the macro from 2 to 8 as I test the data.
For example, if the macro range/evaluation period is 2 cells in the below example.
10 20 30 0 0 0 10 0 0 0 0 0 0 0 0 0 8 9 0 12.

Then the number of repeating evaluation periods would be 10.

If I change the evaluation period to 4 then the return value would be 6.

If I change the evaluation period to 8 then the return value would be 2.

To complicate this further, when the macro starts a new row, it needs to only start the evaluation after the first data point has a value. Meaning if I have empty white cells between the Customer and my first value, the macro will not treat those as zero values.

Any suggestions on how I could start this?

I currently have a solution that is tedious and requires a ton of formulas that is a pain to adjust.

Thanks in advance.


Any suggestions on how I could start this?

Thanks in advance.[/img]

 

[Greg Truby]

Well that's progress!

Try pasting it into cell V1 first and get it to work there. Then you should be able to copy it and make adjustments as needed.

Don't forget that you can use the formula auditing tool to step through a formula.