Advance a column of dates by a month

[Kingchaos64]

I have a table with A5 as my date header and A6:A500 that have Januarys dates in them. I have all the cells formatted to Date (01/15/21). Multiple cells in the A Column have the same date. At the end of each month the workbook is to be saved and a new one is to be started. In the new sheet the days are going to be the same it's just the month that need to change. I need a formula or VBA code that will change the all the dates and advance them one month. I have this VBA


Dim ws As Worksheet
Dim lRow As Long, i As Long

'~~> Change this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row

For i = 6 To lRow
.Range("A" & i).Value = DateAdd("m", 1, .Range("A" & i).Value)
Next i
End With
End Sub

When I run this it will change the month but I get an error code 13 Type Mismatch. Also it doesn't account for the different days of the month 29th, 30th,31st it just leaves those dates and then advancing to March it doesn't do anything with them.
Any ideas.
Also my table has a total row. With word total in the A Column I don't think that's causing a problem but just want to give all the facts

 

[JLGWhiz]

When using functions for dates, such as DateAdd, the dates must be Date data types. If you have not already reviewed the spec sheet for the DateAdd function, here is a link to it.
DateAdd function (Visual Basic for Applications) | Microsoft Docs
Working with dates in Excel can be very unsettling at times because of their special rules and automatic formatting. the type mismatch indicates that your column A reference may not be seen as a date by VBA. If your "Totals" text is locate in column A The try changing this;

For i = 6 To lRow

To this

For i = 6 To lRow - 1

 

[Dave Patton]

If the following works for you, you can make column B values and then Copy and paste column C to A

You would then just change the Year and Month as required.

T202101b.xlsm
	A	B	C
1	Year	2021
2	Month	3
3
4
5	Date
6	2-Jan-21	2	2-Mar-21
7	3-Jan-21	3	3-Mar-21
8	5-Jan-21	5	5-Mar-21
9	5-Jan-21	5	5-Mar-21
10	5-Jan-21	5	5-Mar-21
11
60

Cell Formulas
Range	Formula
B6:B10	B6	=DAY(A6)
C6:C10	C6	=DATE(B$1,B$2,B6)

 

[Kingchaos64]

If the following works for you, you can make column B values and then Copy and paste column C to A

You would then just change the Year and Month as required.

T202101b.xlsm
	A	B	C
1	Year	2021
2	Month	3
3
4
5	Date
6	2-Jan-21	2	2-Mar-21
7	3-Jan-21	3	3-Mar-21
8	5-Jan-21	5	5-Mar-21
9	5-Jan-21	5	5-Mar-21
10	5-Jan-21	5	5-Mar-21
11
60

Cell Formulas
Range	Formula
B6:B10	B6	=DAY(A6)
C6:C10	C6	=DATE(B$1,B$2,B6)

Thanks Dave I think this would be to much as this worksheet template will be sent to multiple locations with multiple people. It needs to be super easy and straightforward to change the month. Since most people using this barely know how to turn on a computer lol.

 

[Dave Patton]

They would just have to specify the month number or we could adjust the formula to work for say Jun or June

 

[Dave Patton]

Either of

T202101b.xlsm
	A	B	C
1	Year	2021
2	Month	4
3
4
5	Date
6	2-Apr-21	2	=DATE(B$1,B$2,B6)
7	3-Apr-21	3
8	5-Apr-21	5
9	5-Apr-21	5
10	5-Apr-21	5
60

Cell Formulas
Range	Formula
C6	C6	=gf(A6)
A6:A10	A6	=DATE(B$1,B$2,B6)

T202101b.xlsm
	A	B	C
1	Year	2021
2	Month	June
3
4
5	Date
6	2-Jun-21	2	=DATE(B$1,nMonth,B6)
7	3-Jan-21	3
8	5-Jan-21	5
9	5-Jan-21	5
10	5-Jan-21	5
4d

Cell Formulas
Range	Formula
C6	C6	=gf(A6)
A6:A10	A6	=DATE(B$1,nMonth,B6)

The named formula is nMonth =MONTH(1&LEFT('4d'!B1048573,3)) It is assigned to the sheet named 4d

 

[Kingchaos64]

Either of

T202101b.xlsm
	A	B	C
1	Year	2021
2	Month	4
3
4
5	Date
6	2-Apr-21	2	=DATE(B$1,B$2,B6)
7	3-Apr-21	3
8	5-Apr-21	5
9	5-Apr-21	5
10	5-Apr-21	5
60

Cell Formulas
Range	Formula
C6	C6	=gf(A6)
A6:A10	A6	=DATE(B$1,B$2,B6)

T202101b.xlsm
	A	B	C
1	Year	2021
2	Month	June
3
4
5	Date
6	2-Jun-21	2	=DATE(B$1,nMonth,B6)
7	3-Jan-21	3
8	5-Jan-21	5
9	5-Jan-21	5
10	5-Jan-21	5
4d

Cell Formulas
Range	Formula
C6	C6	=gf(A6)
A6:A10	A6	=DATE(B$1,nMonth,B6)

The named formula is nMonth =MONTH(1&LEFT('4d'!B1048573,3)) It is assigned to the sheet named 4d

Oh ok that might work . I'll try it when I get to work thank you

 

[Dave Patton]

Thanks for the feedback.

N.B. I do not know your day sequence; I just included the following for interest sake.

T202101b.xlsm
	A	B	C
1	Year	2021
2	Month	4
3
4
5	Date
6	2-Apr-21		=DATE(B$1,B$2,ROW()-4)
7	3-Apr-21
8	5-Apr-21
9	5-Apr-21
10	5-Apr-21
11
60

Cell Formulas
Range	Formula
C6	C6	=gf(A6)
A9,A6:A7	A6	=DATE(B$1,B$2,ROW()-4)
A8	A8	=DATE(B$1,B$2,ROW()-3)
A10	A10	=DATE(B$1,B$2,ROW()-5)

 

[JLGWhiz]

Another way to avoid the type mismatch with dates is

For i = 6 To lRow
    If IsDate(.Cells(i, 1).Value) Then
        .Range("A" & i).Value = DateAdd("m", 1, .Range("A" & i).Value)
    End If
Next i

Use an IF...Then statement to eliminate non date values.

 

[Kingchaos64]

Another way to avoid the type mismatch with dates is

For i = 6 To lRow
    If IsDate(.Cells(i, 1).Value) Then
        .Range("A" & i).Value = DateAdd("m", 1, .Range("A" & i).Value)
    End If
Next i

Use an IF...Then statement to eliminate non date values.

Thank you very much. The -1 in your previous comment worked such a simple fix. I might change it to this code just to avoid any issues in the future