Adding 7 days to a date using macro

**ninjao** · 02-25-2009, 11:54 AM

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.

Sub new_week()
'
' new_week Makro
' Makro am 25.02.2009 von  rs aufgezeichnet
'

'
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Selection.ColumnWidth = 10.71
    Range("C21:C30").Select
    Selection.Copy
    Range("B21").Select
    ActiveSheet.Paste
    Range("B21").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("B22").Select
    Selection.ClearContents
    Range("B23").Select
    Selection.ClearContents
    Range("B24").Select
    Selection.ClearContents
    Selection.ClearContents
    Range("B25").Select
    Selection.ClearContents
    Range("B26").Select
    Selection.ClearContents
    Range("B27").Select
    Selection.ClearContents
    Range("B28").Select
    Selection.ClearContents
    Range("A15").Select
    ActiveCell.FormulaR1C1 = "New week added!"
    Range("A13").Select
End Sub

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** · 02-26-2009, 02:53 AM

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

**ninjao** · 02-26-2009, 05:04 AM

Originally Posted by 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

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** · 02-26-2009, 03:53 AM

Whilst cleaning the code, remove the unnecessary selections

 Range("C21:C30").Select
    Selection.Copy
    Range("B21").Select
    ActiveSheet.Paste

Can be reduced to

Range("C21:C30").Copy   Range("B21")

**royUK** · 02-26-2009, 05:13 AM

Not sure what you want exactly, but something like

If IsDate(Range("B1")).Value Then Range("C1").Value = Range("B1").Value + 7

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

**ninjao** · 02-26-2009, 05:27 AM

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:

Sub new_week()
'
' new_week Makro
' Makro am 25.02.2009 von  rs aufgezeichnet

    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Selection.ColumnWidth = 10.71
    Range("C21:C30").Copy Range("B21")
    Range("B21:B28").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    If IsDate(Range("B1")).Value Then Range("C1").Value = Range("B1").Value + 7
    Range("A1").Select
    MsgBox "Week Added!"
End Sub

**royUK** · 02-26-2009, 05:52 AM

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** · 02-26-2009, 06:24 AM

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** · 02-26-2009, 06:31 AM

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

**ninjao** · 02-26-2009, 06:40 AM

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** · 02-26-2009, 07:21 AM

Try this

   '
    Dim rng    As Range
    Dim cl     As Range
    '
    Columns("B:B").Insert Shift:=xlToRight
    Set rng = Range(Cells(21, 3), Cells(21, 3).End(xlDown))
    For Each cl In rng
        cl.Offset(0, -1).Value = cl.Value + 7
    Next cl

**ninjao** · 02-26-2009, 08:51 AM

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

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** · 02-26-2009, 10:03 AM

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** · 02-26-2009, 10:24 AM

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** · 02-26-2009, 11:43 AM

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

**ninjao** · 02-26-2009, 12:17 PM

Yeah I figured as much.

Damn I just can't get it to work

**squiggler47** · 02-26-2009, 01:01 PM

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)

Private Function NextWeek(Strng As String, yearS As String) As String

    Dim DateValue As Date
    
    DateValue = Left(Strng, 2) & "/" & Mid(Strng, 4, 2) & "/" & Right(yearS, 4)
    DateValue = DateValue + 7
    
    NextWeek = Left(DateValue, 2) & "." & Mid(DateValue, 4, 2) & ".-"
    DateValue = DateValue + 6
    NextWeek = NextWeek & Left(DateValue, 2) & "." & Mid(DateValue, 4, 2)&"."

End Function

Private Function NextWeekNum(Strng As String, yearS As String) As String
    
    Dim DateValue As Date
    
    DateValue = Left(Strng, 2) & "/" & Mid(Strng, 4, 2) & "/" & Right(yearS, 4)
    DateValue = DateValue + 13
    
    NextWeekNum = Format(Application.WorksheetFunction.WeekNum(DateValue), "00") & " / " & Right(DateValue, 4)

End Function

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

    Range("b21").Value = NextWeek(Range("C21"), Range("C22"))
    Range("b22").Value = NextWeekNum(Range("C21"), Range("C22"))

**ninjao** · 02-27-2009, 06:11 AM

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:

    DateValue = Left(Strng, 2) & "/" & Mid(Strng, 4, 2) & "/" & Right(yearS, 4)

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

Sub new_week()
'
' new_week Makro
' Makro am 25.02.2009 von  rs aufgezeichnet

    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Selection.ColumnWidth = 10.71
    Range("C21:C30").Copy Range("B21")
    Range("B21:B28").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    

    Range("b21").Value = NextWeek(Range("C21"), Range("C22"))

End Sub



Private Function NextWeek(Strng As String, yearS As String) As String

    Dim DateValue As Date
    
    DateValue = Left(Strng, 2) & "/" & Mid(Strng, 4, 2) & "/" & Right(yearS, 4)
    DateValue = DateValue + 7
    
    NextWeek = Left(DateValue, 2) & "." & Mid(DateValue, 4, 2) & ".-"
    DateValue = DateValue + 6
    NextWeek = NextWeek & Left(DateValue, 2) & "." & Mid(DateValue, 4, 2) & "."
    MsgBox "Woche hinzugefügt!"
    
End Function

Do you know what the problem might be?

Thanks for the help so far guys!

**squiggler47** · 02-27-2009, 06:56 AM

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** · 02-27-2009, 07:15 AM

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

Private Function NextWeek(Strng As String, yearS As String) As String

    Dim DateValue As Date
    
    DateValue = Left(Strng, 2) & "/" & Mid(Strng, 4, 2) & "/" & Right(yearS, 4)
    DateValue = DateValue + 7
    
    NextWeek = Left(DateValue, 2) & "." & Mid(DateValue, 4, 2) & ".-"
    DateValue = DateValue + 6
    NextWeek = NextWeek & Left(DateValue, 2) & "." & Mid(DateValue, 4, 2) & "."

End Function

Private Function NextWeekNum(Strng As String, yearS As String) As String
    
    Dim DateValue As Date
    
    DateValue = Left(Strng, 2) & "/" & Mid(Strng, 4, 2) & "/" & Right(yearS, 4)
    DateValue = DateValue + 7
    
    NextWeekNum = Right("00" & DatePart("ww", DateValue, vbSunday, vbFirstFourDays), 2) & " / " & Right(yearS, 4)
    

End Function

Adding 7 days to a date using macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Re: Adding 7 days to a date using macro

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions