• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset, Loop, and Report Generation


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!!



Staff member

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.



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.