• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

Edit to Copy down column code VBA

The code below puts a static value of 2015/03/31 in cell K3 then copies it down column K until there is no data in column G.  How would I edit this to do the following?

1. Have the MyRange.Value = todays date plus 7 days
2. As it copies that value down column K, as long as there is data in column G, have it increment to the next day for each row.  So today is the 22nd, it would put the 29th in K3, 30th in K4, 31st in K5.....  and on down, until column G is empty.

''    Set MyRange = Range(Range("K3"), Range("K" & Range("G3").End(xlDown).Row))
''    MyRange.Value = "2015/03/31"  

Open in new window

3 Solutions

pls try

Sub Macro1()

    Set MyRange = Range(Range("K3"), Range("K" & Range("G3").End(xlDown).Row))
    MyRange.Resize(1, 1).AutoFill Destination:=MyRange, Type:=xlFillDays
    ' Or as a formula
    'MyRange.Formula = "=TODAY()+7+ROW(K3)-3"
End Sub

Open in new window

Saurabh Singh TeotiaCommented:
You can perhaps use this to do what you are looking for...

myrange.Value = DateAdd("d", 7, Date)

lr = Cells(Cells.Rows.Count, "G").End(xlUp).Row

Range("K3:K" & lr).Formula = "=(today())+(row()-2)"
Range("K3:K" & lr).Value = Range("K3:K" & lr).Value

Open in new window

NorieVBA ExpertCommented:
Try this.
Sub FilDate()
Dim MyRange As Range

    Set MyRange = Range("K3", Range("G" & Rows.Count).End(xlUp).Offset(, 4))
    With MyRange

        .Cells(1, 1).Value = Date + 7
        .DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
        xlDay, Step:=1
        .NumberFormat = "yyyy/mm/dd"
    End With

End Sub

Open in new window

Rob HensonFinance AnalystCommented:
Will columns J or L also have data to the same extent as G?

If so, I would suggest you don't need VBA.

In K3 formula:

In K4 down:

When data expands, select K3 and then press End and Down arrow which takes cursor to last populated cell in column K. A Double click on the bottom right corner of this cell will extend the formula to the last cell in K corresponding to extent of data in J or L. End and Up arrow will then take cursor back to top.

Rob H
RWayneHAuthor Commented:
Thanks for the help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now