Kingchaos64
New Member
- Joined
- Jan 15, 2021
- Messages
- 46
- Office Version
- 365
- Platform
- Windows
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
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
