Filling in a Date Series using the Fill | Series menu command

[Guest]

Using the Fill | Series dialog box, I want to fill in a series of dates, by
month, where I know the beginning date, the end date, and the number of
months. The 'Stop Value' box is only allowing me to enter a date string
(e.g. '12/31/2005'). Instead, I want to enter in a cell reference, to a cell
that has the end date. Is there a way, or a way to write a macro that will
fill in the series, referencing begin dates and end dates contained in other
cells?

 

[Guest]

Hi Bob,
I am a little confused. Filling a date with a series refers to a range. The
top of the range generally will have the start date and what is filled will
depend on the range selected. The increment is something you will supply. The
number of months indicates the the range to select and you will not need to
provide and ending date. Maybe I am missing something here?

 

[Guest]

David,

I'm new to this, so I don't have a good handle on the terminology. Yes, I
am filling in a range (in this case a row of cells). The begin date and end
dates are variables, which are entered elsewhere in the sheet. I then want
the range to be filled in with the series of months, e.g. "Jan-05", "Feb-05",
etc. After I posted this, I struggled for a while, and came up with this:

-
Range("B42").Select
ActiveCell.FormulaR1C1 = "=+R[-5]C"
Range("B42").Select
Selection.DataSeries Rowcol:=xlRows, Type:=xlChronological,
Date:=xlMonth _
, Step:=1, Stop:=Range("B38"), Trend:=False
Range("B42").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.NumberFormat = "mmm-yy"
-
In this case, the value contained in the cell pointed to with "=+R[-5]C" is
the start date, and cell B38 contains the end date. It worked. Then my
problem grew -- if you're interested, see my post "Selecting a variable
number of cells in a row", which goes to the next problem, how to fill in
ranges of cash flows over time (below the range of dates).

Bob

 

[Guest]

Hi Bob,

Do not try and put in an end date, the number of periods will do that for
you. YOu have the start date in B42 and what i have put in is the number of
periods, which is supplied in B38. The line - Range(ActiveCell.Address & ":"
& "B" & (ActiveCell.Row + Periods)).Select - will select the range and the
fill will cover the number of periods needed. The cash flows are below the
dates? The cash flows are for the dates indicated? Just what are you trying
to accomplish in the end here? I could not find the post you refer to, so it
is difficult to figure out what you are trying to accomplish in the end
result. Looking for an NPV? A simple sum? If you post here I will know about
it and get a notification.

David

Sub DatetoFill()
Range("B42").Select
ActiveCell.FormulaR1C1 = "=+R[-5]C"
Periods = Range("B38").Value
Range(ActiveCell.Address & ":" & "B" & (ActiveCell.Row + Periods)).Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlMonth, Step:=1, Trend:=False
End Sub

David,

I'm new to this, so I don't have a good handle on the terminology. Yes, I
am filling in a range (in this case a row of cells). The begin date and end
dates are variables, which are entered elsewhere in the sheet. I then want
the range to be filled in with the series of months, e.g. "Jan-05", "Feb-05",
etc. After I posted this, I struggled for a while, and came up with this:

-
Range("B42").Select
ActiveCell.FormulaR1C1 = "=+R[-5]C"
Range("B42").Select
Selection.DataSeries Rowcol:=xlRows, Type:=xlChronological,
Date:=xlMonth _
, Step:=1, Stop:=Range("B38"), Trend:=False
Range("B42").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.NumberFormat = "mmm-yy"
-
In this case, the value contained in the cell pointed to with "=+R[-5]C" is
the start date, and cell B38 contains the end date. It worked. Then my
problem grew -- if you're interested, see my post "Selecting a variable
number of cells in a row", which goes to the next problem, how to fill in
ranges of cash flows over time (below the range of dates).

Bob

Hi Bob,
I am a little confused. Filling a date with a series refers to a range. The
top of the range generally will have the start date and what is filled will
depend on the range selected. The increment is something you will supply. The
number of months indicates the the range to select and you will not need to
provide and ending date. Maybe I am missing something here?