How to repeat a subset of commands in VBA?

[jordanabu]

Hi everyone, VBA noob here. I'm trying to make a robust attendance tracker for my director, and am approaching the finish line. On the first sheet is the roster of people with adjacent drop-downs to select "here" or "absent,", and on the second sheet is a tracker that records the date each time someone is marked absent and counts the total.

I have created a button that "resets" the form, which I've defined as changing everyone's status back to "here," and converts the formulas on the second sheet that record the date to their value (like F9 function), then copies the same formula over 1 cell. Here's my code:

Please Login or Register  to view this content.

This works successfully with one person, and now I just want to repeat the first 3 commands through the range B3:B47.
Would this require a Loop?

Thanks,
JordanAbu

[mrice]

Try

Please Login or Register  to view this content.

[jordanabu]

Thanks Martin! It didn't work at first, but I realized it was because I needed to re-reference the Sheet (the command button is located on a different sheet)

I then realized, though, that I have another issue:
I need "B5" in the formula below to instead be a variable also, which cycles through these ranges in this order: B5:B14, D5:D15, F5:F17, H5:H16

IF(OR(INDIRECT(""RC[-1]"",0)=Form!$C$2,INDIRECT(""RC[-1]"",0)=""""),"""",IF(Form!$B5=""Absent"",TODAY(),""""))"

This will equal the 46 cycles of N (2 to 47). I'm not sure this is possible, and I suppose I could create 4 different subs - one for each column, but the less code the better!

Thanks,
JordanAbu

[mrice]

I would suggest defining a name for the range B5:B14, D5:D15, F5:F17, H5:H16 and then using a for Each loop to sequentially create you formulae.