Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill-In Rest Of Column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill-In Rest Of Column
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill-In Rest Of Column
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill-In Rest Of Column
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" wrote in message ... 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill-In Rest Of Column
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" wrote: 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" wrote in message ... 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up field and auto fill the rest | Excel Worksheet Functions | |||
Unable to copy the merged cell to the rest of the column | Excel Worksheet Functions | |||
Fill a column with the contents of another column based on a choic | Excel Discussion (Misc queries) | |||
Trying to find a way to fill in the rest of a row if it is a dupli | Excel Worksheet Functions | |||
How do i keep a column stationary but move the rest of the sheet? | Excel Discussion (Misc queries) |