EDATE problem

[0 Agios]

what i want to do is add a new month, so if the previous cell says Aug 23 the new one should say Sept 23
this is my code and a sample of the page


Private Sub CommandButton1_Click()
ActiveCell.EntireColumn.Select
ActiveCell.EntireColumn.Copy ActiveCell.Offset(0, 3).EntireColumn
Selection.Copy
ActiveCell.Offset(0, 1).ColumnWidth = 1
ActiveCell.Offset(0, 2).PasteSpecial Paste:=xlPasteAll
ActiveCell.Offset(0, 0) = "=EDATE(R[-3]C,0)"
End Sub

 

[Peter_SSs]

Your original line said

ActiveCell.Offset(0, 0) = "=EDATE(R[-3]C,0)"

The post #2 suggestion was to simply change the EDATE formula

ActiveCell.Offset(0, 0) = "=EDATE(RC[-3],1)"

Why have you further changed it to

ActiveCell.Offset(0, 0).Value = "=EDATE(ActiveCell.Offset(-3, 0), 1)"

BTW, there is no need for a zero offset so this would work just as well

ActiveCell = "=EDATE(RC[-3],1)"

 

[NoSparks]

How about trying this ?

Sub CommandButton1_Click()
    Dim LastCol As Long
    Dim startCell As Range
    Dim oldDate As Date
    
'locate last existing columns
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
'set the position to work from
Set startCell = Cells(1, LastCol + 3)

With startCell
    'the narrow column
    .Offset(, -1).ColumnWidth = 1
    'copy previous columns
    .Offset(, -3).Resize(, 2).EntireColumn.Copy
    .PasteSpecial Paste:=xlPasteAll
    'previous date was copied so use it
    oldDate = .Value
    'new date
    .Value = DateAdd("m", 1, oldDate)
    'stop the marching ants
    Application.CutCopyMode = False
    'position the cursor
    .Select
End With

End Sub

 

[0 Agios]

Finally it works, Thank you Sparks, Peter