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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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

NorieAnalyst Assistant Commented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.