Regular deposits at regular weekly intervals

The fact that you are showing data in the past, and wanting to place deposits at an arbitrary interval, and remove "excess" deposits when you change the interval, suggests to me that you are running some sort of simulation. If that is true, then you can do this with formulas rather than VBA. Fortunately you have already set up your data layout to be friendly for such a solution. I have updated your file to show you what I mean.

I have assumed that you want your first deposit to occur on June 2, 2015, regardless of the interval. Then the formula determines the interval after that by counting whether the specified interval in weeks has passed:

Formula:

Please Login or Register  to view this content.

If a holiday falls on a Tuesday and a deposit would normally be due that day, this algorithm will skip that deposit. This can happen on July 4, Christmas, and New Year's.

I would recommend that column I be labelled "Shares" instead of "Stocks" but I didn't change it.

Also I can't make sense of the formula you described above for June 2, 2015. I think you want

Formula:

Please Login or Register  to view this content.

I don't think your code will be useful. However, in the interests of learning, the reason your VBA doesn't work is that you are mixing the R1C1 formula format with the A1 format. The correct way to write your code would be

Please Login or Register  to view this content.