VBA to subtract 6 days from a date

[akn846]

I have a spreadsheet which as the first column currently contains a date - which is the date of the Sunday of the given week.

What I am looking to do is to populate a second field with the date of the Monday of the same week.

I know I can do this using the DATE function within Excel, but as this spreadsheet is going to keep growing, I need a means of doing it for as many rows which are in the sheet.

So could someone suggest how I might go about doing this in VBA.

Many thanks

 

[reszek]

do a syntax search for the command in VBA called dateadd, i think that should work, havent used it in a while though. make sure u put the value of -6 as the number u wish to dateadd

 

[akn846]

thanks very much - I shall give this a go, dateadd is indeed the command

Thanks again

 

[bryanbayfield]

eg.

Sunday dates are in column "A", starting at row2.
Monday dates should be in column "B", starting at row2.

You should:

Put "=A2-6" in cells "b2"
Fill down.

Simple as that.

No need for VBA unless you would like to automate the population of column "B" as column "A" changes. SuperBry!

 

[akn846]

Bryan

Thanks for that - but I would prefer to automatically populate the week start field.

Another silly question coming up - I know how to count he number of rows in the spreadsheet - so I wanted to loop through the process of changing all the B values.

How can I use a variable name to access a cell - ie I have to loop through and use the activeworksheet.range("a1&quot construct to assign the value of this cell to a variable - how do I make that call activeworksheet.range('variable name')

Thanks once again for your help

 

[akn846]

Ignore me - I've cracked it!!!