Adding 7 days to a date using macro

[ninjao]

Hi!

It would be really great if you could help me with my macro that I am trying to create for my spreadsheet.

Basically it's to make my content management easier.

I have made a button and assigned a macro to it. At the moment the button creates a new column and makes it the right size etc.

Please Login or Register  to view this content.

However what I really want is for the newly created cell to take the date from the cell next to it and add 7 days to it. For example: 04.01.-10.01. (is the original date) and 11.01.-18.01. (would be the new date)

Any kind of workaround(other way of formatting the date) or new logical process you have for doing what I want would be really great!

P.S im using Excel 2000 (German version)

Thanks in advance!

-Ninjao

[mudraker]

The macro code you posted needs a lot of cleaning up

why are you copying cells c21:c30 to b21:30 then clearing b21:28

In what cell is the 04.01.-10.01 entry

[royUK]

Whilst cleaning the code, remove the unnecessary selections

Please Login or Register  to view this content.

Can be reduced to

Please Login or Register  to view this content.

[ninjao]

The macro code you posted needs a lot of cleaning up

why are you copying cells c21:c30 to b21:30 then clearing b21:28

In what cell is the 04.01.-10.01 entry

Yeah I just posted the rough macro code I've already cleaned it up.

The Cell that contains the date is: B21

Will it be possible to do what I plan to do?

Thanks!

[royUK]

Not sure what you want exactly, but something like

Please Login or Register  to view this content.

It's best to post cleaned up code, it's easier to follow.

[ninjao]

Thanks for the quick reply!

I just tried adding your code to my macro and I got the following error "Invalid Identifier" and it highlights the code "IsDate" (well the error was actually in german but thats what I translate it as)

Here is my current macro:

Please Login or Register  to view this content.

[royUK]

Looking at your code you clear contents in column B, so you can't add 7 days to any value there. In fact you copy C1 to C28 to B then delete it.

What exactly are you doing?

[ninjao]

Oops sorry you are correct it would be C21.

I attached an image to illustrate what I imagine. Maybe it will make it clearer

Here is a link incase the attachment doesn't work

[royUK]

The attachment does work, but a workbook would be more useful. Are you copying C to B & adding 7 days in B?

[ninjao]

Yes, I'm copying C to the newly created, empty B. And then adding 7 days to B.

I would add the workbook but it contains sensitive company related data

[royUK]

Try this

Please Login or Register  to view this content.

[ninjao]

That code did the following for me: It added a new column and then filled out the range B21:B29 with the following:

Please Login or Register  to view this content.

Damn I guess this one is quite hard. Do you have any other ideas how I might be able to achieve a similar effect? Maybe with a different Date Layout..?

Thanks for helping me so far!

[royUK]

Attach a dummy workbook, the code adds a new Column B , * copies the dates in C adding 7 days. There must be dates in Column C.

[ninjao]

Thanks Roy, I see how your's works.

I added a "basic" version of what I am working on(minus sensitive data) so you can have a better look at my problem. In the Excel file I have added additional info for you so you can see what my project is about and hopefully have a better understanding of what I'm working on!

-Ninjao

p.s. Woche hinzufügen means "Add week"

[royUK]

Your dates are text, you cannot add 7 days to a text entry

[ninjao]

Yeah I figured as much.

Damn I just can't get it to work

[squiggler47]

You could try these 2 functions :-

They take "04.01.-10.01." and "01 / 2009" as arguments

and

return 11.01.-17.01 and 03 / 2009 respectively (weeknum used excels weeknum function, so it doesnt agree with yours but you can adjust)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Add this to your code in place of isdate....

Please Login or Register  to view this content.

[ninjao]

Wow thanks! That helped me a lot. It currently works... somewhat. The date does get the update. However I didn't add the week number function because for some reason it wouldn't execute at all.

I also don't REALLY need the weeknumber counter...however when I try to press the button again to add a new week(just to test if it works again) I get an error:

"Runtime error 13"

and in the debugger it highlights this bit of code:

Please Login or Register  to view this content.

Here is my full macro, in it's current state:

Please Login or Register  to view this content.

Do you know what the problem might be?

Thanks for the help so far guys!

[squiggler47]

Yes function needs 2 parameters dates in format "dd.mm.-dd.mm." and year "51 / 2008", if either of these is blank the function wont work!

you can call the function :-

Range("b21").Value = NextWeek(Range("C21"), Date$) to make it work using todays year!

the second function didnt work, because dummy that i am didnt test it in excel 2000!

[squiggler47]

repaired functions :-

You need to have both since you have the year and the date in 2 different cells, so both cells have to be updated

I attached the worksheet which works on my copy of excel 2000

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[ninjao]

Oh wow thanks that works great!!


Now on to the even harder stuff Importing data from a .csv !