Fill-In Rest Of Column

B

Bob Barnes

I posted this yesterday w/ no answers. So, I'm re-wording it.

I am running Access-to-Excel automation to
fill an Excel Column w/ Dates. It is setup to handle
52 Dates, but has only 40 weeks data so far.

So, IF "Column A" fills in 40 of the cells, it leaves the other 12 blank.

I need those other 12-cells filled in with W/E dates so a Trendline
will display on an Excel Chart as a "Target". Now, I just copy & paste
in the remaining 12 Dates.

I started recording this macro...

Application.Goto Reference:="AOne"
Selection.End(xlDown).Select
....rest.... ???
--- I would like to identify the rownumber of that cell (IE "A")
& compare it to the rownumber of the 52nd cell (IE "B"). I can
then "Find" that last-entered datevalue (the Contents in "A")
in a predefined column.

Then copy down "X' number of rows (to match the difference
between ["B" - "A" + 1] above).

Does anyone have existing code to do this?

TIA - Bob
 
J

Jim Thomlinson

This might be a silly question but why exactly do you want to fill in the
remaining dates? In an excel chart (depending on the chart type) you can
automatically add a trendline. I assume that this is what you are doing. When
you right click on that trend line you get an options tab. On that taby one
of the options is to extend the trend line forwards or backwards. Extending
the line automatically fills in the dates on the graph.

So I am left wondering why fill in the dates?
 
B

Bob Barnes

Jim - Thank you for answering.

The automation brings in recorded data for...now 40 weeks.

But Management wants the "Targets" to extend ALL 52 datapoints
on the Chart.

I need the "defined values" (Targets) for certain W/E dates to be reflected
on the Chart. If I extend it forward, it looks like it does a regression
trend, rather than the "defined values". I am using a VLOOKUP to populate
those vales in "C-Column". "A-Column" are Dates & "B-Column" is for observed
values.

Does this clarify?

TIA - Bob

Jim Thomlinson said:
This might be a silly question but why exactly do you want to fill in the
remaining dates? In an excel chart (depending on the chart type) you can
automatically add a trendline. I assume that this is what you are doing. When
you right click on that trend line you get an options tab. On that taby one
of the options is to extend the trend line forwards or backwards. Extending
the line automatically fills in the dates on the graph.

So I am left wondering why fill in the dates?

Bob Barnes said:
I posted this yesterday w/ no answers. So, I'm re-wording it.

I am running Access-to-Excel automation to
fill an Excel Column w/ Dates. It is setup to handle
52 Dates, but has only 40 weeks data so far.

So, IF "Column A" fills in 40 of the cells, it leaves the other 12 blank.

I need those other 12-cells filled in with W/E dates so a Trendline
will display on an Excel Chart as a "Target". Now, I just copy & paste
in the remaining 12 Dates.

I started recording this macro...

Application.Goto Reference:="AOne"
Selection.End(xlDown).Select
...rest.... ???
--- I would like to identify the rownumber of that cell (IE "A")
& compare it to the rownumber of the 52nd cell (IE "B"). I can
then "Find" that last-entered datevalue (the Contents in "A")
in a predefined column.

Then copy down "X' number of rows (to match the difference
between ["B" - "A" + 1] above).

Does anyone have existing code to do this?

TIA - Bob
Click to expand...
Click to expand...
 
T

Tom Ogilvy

After you data is in the worksheet. Turn on the macro recorder, then select
the last two dates and drag fill down. Turn off the macro recorder and
Adjust the code to achieve your objective.

--
Regards,
Tom Ogilvy

Bob Barnes said:
Jim - Thank you for answering.

The automation brings in recorded data for...now 40 weeks.

But Management wants the "Targets" to extend ALL 52 datapoints
on the Chart.

I need the "defined values" (Targets) for certain W/E dates to be reflected
on the Chart. If I extend it forward, it looks like it does a regression
trend, rather than the "defined values". I am using a VLOOKUP to populate
those vales in "C-Column". "A-Column" are Dates & "B-Column" is for observed
values.

Does this clarify?

TIA - Bob

Jim Thomlinson said:
This might be a silly question but why exactly do you want to fill in the
remaining dates? In an excel chart (depending on the chart type) you can
automatically add a trendline. I assume that this is what you are doing. When
you right click on that trend line you get an options tab. On that taby one
of the options is to extend the trend line forwards or backwards. Extending
the line automatically fills in the dates on the graph.

So I am left wondering why fill in the dates?

Bob Barnes said:
I posted this yesterday w/ no answers. So, I'm re-wording it.

I am running Access-to-Excel automation to
fill an Excel Column w/ Dates. It is setup to handle
52 Dates, but has only 40 weeks data so far.

So, IF "Column A" fills in 40 of the cells, it leaves the other 12 blank.

I need those other 12-cells filled in with W/E dates so a Trendline
will display on an Excel Chart as a "Target". Now, I just copy & paste
in the remaining 12 Dates.

I started recording this macro...

Application.Goto Reference:="AOne"
Selection.End(xlDown).Select
...rest.... ???
--- I would like to identify the rownumber of that cell (IE "A")
& compare it to the rownumber of the 52nd cell (IE "B"). I can
then "Find" that last-entered datevalue (the Contents in "A")
in a predefined column.

Then copy down "X' number of rows (to match the difference
between ["B" - "A" + 1] above).

Does anyone have existing code to do this?

TIA - Bob
Click to expand...
Click to expand...
Click to expand...
 
B

Bob Barnes

Tom - Thank you. I will try tha.

I also figured out another way. Write the Query in Access so the "most
recent 52 weeks" will appear in the Query.

Bob

Tom Ogilvy said:
After you data is in the worksheet. Turn on the macro recorder, then select
the last two dates and drag fill down. Turn off the macro recorder and
Adjust the code to achieve your objective.

--
Regards,
Tom Ogilvy

Bob Barnes said:
Jim - Thank you for answering.

The automation brings in recorded data for...now 40 weeks.

But Management wants the "Targets" to extend ALL 52 datapoints
on the Chart.

I need the "defined values" (Targets) for certain W/E dates to be reflected
on the Chart. If I extend it forward, it looks like it does a regression
trend, rather than the "defined values". I am using a VLOOKUP to populate
those vales in "C-Column". "A-Column" are Dates & "B-Column" is for observed
values.

Does this clarify?

TIA - Bob

Jim Thomlinson said:
This might be a silly question but why exactly do you want to fill in the
remaining dates? In an excel chart (depending on the chart type) you can
automatically add a trendline. I assume that this is what you are doing. When
you right click on that trend line you get an options tab. On that taby one
of the options is to extend the trend line forwards or backwards. Extending
the line automatically fills in the dates on the graph.

So I am left wondering why fill in the dates?

:

I posted this yesterday w/ no answers. So, I'm re-wording it.

I am running Access-to-Excel automation to
fill an Excel Column w/ Dates. It is setup to handle
52 Dates, but has only 40 weeks data so far.

So, IF "Column A" fills in 40 of the cells, it leaves the other 12 blank.

I need those other 12-cells filled in with W/E dates so a Trendline
will display on an Excel Chart as a "Target". Now, I just copy & paste
in the remaining 12 Dates.

I started recording this macro...

Application.Goto Reference:="AOne"
Selection.End(xlDown).Select
...rest.... ???
--- I would like to identify the rownumber of that cell (IE "A")
& compare it to the rownumber of the 52nd cell (IE "B"). I can
then "Find" that last-entered datevalue (the Contents in "A")
in a predefined column.

Then copy down "X' number of rows (to match the difference
between ["B" - "A" + 1] above).

Does anyone have existing code to do this?

TIA - Bob
Click to expand...
Click to expand...
Click to expand...
 
Top