Offset, Loop, and Report Generation

[ShawnExcel]

So here's a tough one I have been struggling with. Well...tough to me.

I have attached an excel file. I'd like to use anything in green (i.e. only numbers with significant results AND scientific evidence behind it) to create a report. I am trying to get VBA that will loop through row 12, identify anything with a 1 (not 0). From my understanding, the code should:

• Count the number of 1s in row 12
• Create the appropriate number of boxes/formatting, as shown in rows 18 and down
• Loop through and fill in the data in each box

That should be it? The trouble I'm having is with looping through / offsetting. Anything with the black background is the report that is being generated, and I only added it here as an example. It'll end up being on another page in a different format but I can code that - my struggle is getting it to loop through in the first place.

Any help would be appreciated!!

 

[r1c1]

@ShawnExcel

Thanks for the question. Although VBA can be used, I think you can also get the report with a simple formula.

Here is the formula approach.

I have set up an extra row to calculate the cumulative number and then used INDEX, MATCH to fetch corresponding report items. This assumes each report is 4 rows tall (3 rows for the report and one blank row).

See attached.

 

[ShawnExcel]

Thank you so much! It took me a while to decipher exactly how you did that but it makes a lot of sense. Thank you for the help.