VBA to insert this first monday of the week date function/value

[line1260skr]

I am trying to replace my worksheet function which generates a date for the first monday of the weekday when given a date. I have managed to create a VBA code that works to insert this function in to cells. It is very ugly and slow and may make some of you sick to your stomach. I was hoping someone could provide a more reasonable way for trying to accomplish this task.

Please Login or Register  to view this content.

[xLJer]

If you would like to use a worksheet formula, something like this may
work for you by calculating just Monday dates.

If you try it, with a starting date typed in cell A2, add this formula to A3: "=A2+1",
and copy this formula down some, choose a date format for column A cells to show the weekday name,
then you will see that the formula in column L displays the Monday date for the days
Saturday through the following Friday. Use a date format to show weekday names in column L as well and copy it down as needed.

Please Login or Register  to view this content.

[line1260skr]

That code works well when I paste it into Column L and copy it down. However, how would I rewrite that code to have the vba populate the cell for me (either with the formula itself or the value)?

A-K are part of an exported .xls file and I will need column L to create itself.

[line1260skr]

I tried incorporating the code from post #2 into my code from post #1 and got:

Please Login or Register  to view this content.

The result is that it copies the same formula placed in cell A2 all the way down. It stops at the correct row but the row references are not changing. What does the code look like to have these cell references change as they are copied down??

[xLJer]

Here is your code, revised to calculate Monday dates based on the date
listed in column A, and put them in column L. This code defines the week
as Sunday through Saturday, but you can use the alternate Saturday through Friday
code commented out, if needed.

Your loop was not coded correctly, as it was just duplicating the same thing for each row.
Since you chose to use AutoFill, I removed the unnecessary looping code.

Please Login or Register  to view this content.