CODEWITHSUNDEEP
vbaexcel
Roger
Roger

Reputation: 395

Fill the dates in the middle knowing the starting and ending dates

I guess this could be done in other languages, but I have to use VBA, which I am newbie.

There are two lists that select dates (all are the end of months) in sheet1. Once they are selected, they are the starting and ending dates. The starting date will be copied to A1 of sheet 2. I hope there is a way to dynamically fill the rows of column A until the ending dates. Any way, either through VBA or Excel formula to do it? Thanks!

enter image description here

Upvotes: 1

Views: 411

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

You could do this without VBA, using a helper column. In your new spreadsheet where you have a full list of dates, put this formula next to it. For ease of use, I've named a cell startDate and endDate which we can use.

=IF(AND(A5>=startDate,A5<=endDate),A5,"")

Where column A are all the dates, the formula is in column B. The dates appear/disappear as timeframe is adjusted.

enter image description here

Edit: As @ScottHoltzman astutely pointed out, you can actually skip using a helper column. In A5, =startDate then in A6 put this and fill down: = IF(A5+1<=endDate,A5+1,"")

Upvotes: 1

user4039065
user4039065

Reputation:

In VBA with startdate in Sheet1!B1 and enddate in Sheet1!B2 as,

With Worksheets("sheet2").Columns("A")
    .Cells.Clear
    .Cells(1) = Worksheets("sheet1").Cells(1, "B").Value2
    .DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:=xlDay, _
                Step:=IIf(Worksheets("sheet1").Cells(2, "B").Value2 > Worksheets("sheet1").Cells(1, "B").Value2, 1, -1), _
                Stop:=Worksheets("sheet1").Cells(2, "B").Value2
    Intersect(.Cells, .Cells.Parent.UsedRange).NumberFormat = "m/d/yyyy"
End With

Upvotes: 1

Related Questions