-1

I have a row (say row 2) with these 16 values (A-P):

1/1/11 , 1/2/11 , 1/3/11 , 1/4/11 ... ... ... 1/2/12 , 1/3/12 , 1/4/12

Would it be possible to create a Macro that when clicked, autofills the cell to the right of it (Q2) with 1/5/12. And when clicked again it autofills the next cell (R2) with 1/6/12 and so on?

I've tried searching everything that has to do with auto-filling but the macros only apply for an array and not the next empty cell one at a time. Thanks and appreciate any help given!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
G. Ang
  • 3
  • 2
  • 2
    Why not just enter the first date, then drag over (using the anchor at the bottom right hand corner)? Excel will automatically increment the date – cybernetic.nomad Nov 07 '18 at 20:39
  • @cybernetic.nomad thanks for the suggestion but my client wants a button that add with everyclick for them instead of them autofilling themselves and recording a macro only work once – G. Ang Nov 07 '18 at 20:42
  • 1
    [Find the last cell used](https://stackoverflow.com/questions/11926972/excel-vba-finding-the-last-column-with-data) in row 2 then insert the value of it in the next cell over, making sure it is formatted as a date. – cybernetic.nomad Nov 07 '18 at 20:49
  • `Cells(2,Columns.Count).End(xlToLeft).offset(,1).value2 = Cells(2,Columns.Count).End(xlToLeft).value2 + 1` – Scott Craner Nov 07 '18 at 21:08

2 Answers2

0

Assign this to your button. Just change the "2" in code to whatever your row is.

Sub autoFillNextCellDate()
    Dim LastCol, currentRow As Integer
    currentRow = 2 'change to whatever row you want to work with
    With ActiveSheet
        LastCol = .Cells(currentRow , .Columns.Count).End(xlToLeft).Column
    End With

    Cells(currentRow , LastCol).AutoFill Destination:=Range(Cells(currentRow , LastCol), Cells(currentRow , LastCol).Offset(0, 1)), Type:=xlFillDefault
End Sub
ThisGuyJustNeedsHelp
  • 115
  • 11
  • It may be a little complicated to have to select the date manually but I am also able to get +1 to the day instead of month using your code. Nonetheless appreciate your effort! – G. Ang Nov 07 '18 at 21:16
  • I've modified my code to fit your need. Couldn't go wrong with either answer. – ThisGuyJustNeedsHelp Nov 07 '18 at 21:43
0

Assign this macro to your button:

Sub AddADate()
    Dim N As Long, d As Date, d2 As Date
    N = Cells(2, Columns.Count).End(xlToLeft).Column
    d = Cells(2, N).Value
    Cells(2, N + 1).Value = DateSerial(Year(d), Month(d) + 1, Day(d))
End Sub

(this assumes you are using d/m/yy formatting)

Gary's Student
  • 95,722
  • 10
  • 59
  • 99