Add a week to a range of dates in VBA

Question

In sheet INPUT, rows of data for week schedule gets inputted. Multiple rows per day is possible. Column A contains the dates of the original week of the schedule. When the complete planning is set in the INPUT sheet (lets say 15 rows by 10 columns of data) a macro can be run which copies the planning to the OUTPUT sheet and creates it for a full year. Therefore I'm coding VBA to copy the block of data and paste it beneath the previous week and update the dates by 7 days.

I'm having difficulties with DateAdd function.

Does anyone know a good solution?

Dim i As Integer
Dim rowCount As Long
Dim columnCount As Long

Sheets("OUTPUT").Select
Sheets("OUTPUT").Cells.Clear
Sheets("INPUT").Select
rowCount = Sheets("INPUT").Range("A6", Sheets("INPUT").Range("A6").End(xlDown)).Rows.Count - 1
columnCount = Sheets("INPUT").Range("A5", Sheets("INPUT").Range("A5").End(xlToRight)).Columns.Count - 1
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("OUTPUT").Select
Range("A1").Select
ActiveSheet.Paste

For i = 1 To 51

    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.Offset(-rowCount, columnCount)).Copy
    Range("A" & Rows.Count).End(xlUp).Select
    ActiveCell.Offset(1, 0).PasteSpecial
    Range("A" & Rows.Count).End(xlUp).Select
    Range(Selection, Selection.Offset(-rowCount, 0)).Value = DateAdd("d", 7, Range(Selection, Selection.Offset(-rowCount, 0)).Value)

Next i

Avoid using Select...http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — Rdster, Dec 05 '16 at 20:32
http://stackoverflow.com/questions/16259166/add-x-number-of-days-to-a-date-with-vba-in-excel/16259223#16259223 — David Zemens, Dec 05 '16 at 20:38
I'm inclined to close this as duplicate unless you can show some additional code that demonstrates how this problem differs from other similar Q's such as one linked above (beyond your `Selection`, which is really very irrelevant to the problem at hand). — David Zemens, Dec 05 '16 at 20:39
In particular, you should probably show your failed attempt at `DateAdd` function. — David Zemens, Dec 05 '16 at 20:40
@DavidZemens This is my first day on this site and I am not an experienced programmer. I have searched and have not found a solution to the problem. I don't see it as a duplicate of the link that you provided or my experience is not adequate to translate it to my problem. Can you please provide help? — JoshuaTievoor, Dec 05 '16 at 23:45
That's not an excuse. As a first-timer, you're bombarded with all sorts of messages that guide you in learning the protocols here, and what are the community's expectations of you. You must've missed it. Take the [tour](http://stackoverflow.com/tour), read about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to create an [MCVE](http://stackoverflow.com/help/mcve). Cheers. — David Zemens, Dec 06 '16 at 14:50
I don't understand why you have to make it more difficult for somebody that is figuring this out. According to you my problem is a duplicate. The link you provided does not mention any offset, which is key to my code. Also you say that selection is not relevant. This is also key to my code as only a selection of my data needs to be adjusted by 7 days. I should have provided more code but knew it was rubbish so I excluded it. My apologies. It should not be difficult for an experienced person I'm guessing. Can you please guide me instead of making it more difficult for me to resolve the issue? — JoshuaTievoor, Dec 06 '16 at 15:10
The link doesn't mention offset, but there's no reason to believe that was your problem (since your initial question didn't really show any code or provide enough detail to ascertain that).. I've removed the "duplicate" flag because you did edit your question with additional detail. As to why I have to "make it more difficult", I could ask you the same thing: why would you make it more difficult for us by ignoring the recommendations here, which are intended to help you create specific and answerable questions with clear problem statements? Help us help you, in other words... — David Zemens, Dec 06 '16 at 16:58
*It should not be difficult for an experienced person I'm guessing.* It's very difficult to *guess* at what you're trying to do, which is why it helps to show your code -- even if it's bad/rubbish code, it's often enough to help ascertain things that you might otherwise have difficulty explaining, and it provides context for further questions which aim to clarify, etc. — David Zemens, Dec 06 '16 at 17:00

score 1 · Answer 1 · answered Dec 05 '16 at 20:36

1

Variable= DateAdd("d", 7, Variable)

answered Dec 05 '16 at 20:36

Doug Coats

6,255
9
27
49

David Zemens · Answer 2 · 2016-12-06T17:24:08.547

0

I'm not entirely sure what you do with columnCount so this may need to be modified, but I think something like this should get you started

Dim dateVals
Dim destRange as Range
Dim cl as Range
For i = 1 To 51
    ' initial data
    ' this gets a range from the last cell in column A, offset by the rowCount variable, and resized based on row/col counters
    Set rng = Range("A" & Rows.Count).End(xlUp).Offset(-rowCount).Resize(rowCount,colu‌mnCount)
    ' similar size range beginning in row after 'rng'
    Set destRange = rng.Offset(rng.Rows.Count)

    ' Instead of copying the cells/paste special, just transfer the values directly
    ' Range(rng, rng.Offset(-rowCount, columnCount)).Copy
    destRange.Value = rng.Value

    ' Add 7 days to each cell value
    ' I assume you only have date values in column 1
    For Each cl in destRange.Columns(1).Cells
        cl.Value = DateAdd("d", 7, CDate(cl.Value))
    Next

Next i

edited Dec 06 '16 at 17:24

answered Dec 06 '16 at 15:13

David Zemens

53,033
11
81
130

The complete context is; in sheet INPUT, rows of data for week schedule gets inputted. Multiple rows per day is possible. When the complete planning is set in the INPUT sheet (lets say 15 rows by 10 columns) a macro can be run which copies the planning to the OUTPUT sheet and creates it for a full year. I experimented with your code but it is only copying the last row. It needs to copy the whole block of 15 by 10 and change the dates for the 15 rows. I've edited the code above to display the complete scenario. – JoshuaTievoor Dec 06 '16 at 16:48
Try `Set rng = Range("A" & Rows.Count).End(xlUp).Offset(-rowCount).Resize(rowCount,columnCount)` – David Zemens Dec 06 '16 at 16:50
I'm getting a type mismatch error '13' when I step through the `cl.Value = DateAdd("d", 7, cl)` – JoshuaTievoor Dec 06 '16 at 17:22
Try `cl.Value = DateAdd("d", 7, CDate(cl.Value))` – David Zemens Dec 06 '16 at 17:23
My apologies but still the same error. Could it be because the dates are in dd-mmm-yyyy format? – JoshuaTievoor Dec 06 '16 at 17:33
Yes, if they're not actually dates. If they're *text strings* that *look like* dates, that can be a problem and there are several questions/answers on SO already that deal with those. Varying locale formats is a major pain in the ass. Good luck... – David Zemens Dec 06 '16 at 17:40

Add a week to a range of dates in VBA

2 Answers2