Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Down Automatically ?
Cell B1 has a formula which generates the row # of the last nonblank cell in
Column E. The current value of cell B1 in my spreadsheet is 10,012. Cell G13 has a formula in it. Cell H264 has a formula in it. Cell AC767 has a formula in it. What I want is for Excel to automatically fill down the formulas from G13, H264 and AC767 to the row number in cell B1 automatically. The result would be that G13 would be filled down to G10012, H264 would be filled down to H10012 and AC767 would be filled down to AC10012. I want Excel to do this automatically instead of me choosing the cells and manually filling down. Does anyone know how to accomplish this? Daniel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Down Automatically ?
You could write a macro to do it. By automatically, do you mean when the
value in B1 changes, then the cells would be filled down? How about if B1 decreases - would formulas be cleared. How does the data in the last row get added (so that B1 would change)? Manually? -- Regards, Tom Ogilvy "Daniel Rascoe" wrote in message ... Cell B1 has a formula which generates the row # of the last nonblank cell in Column E. The current value of cell B1 in my spreadsheet is 10,012. Cell G13 has a formula in it. Cell H264 has a formula in it. Cell AC767 has a formula in it. What I want is for Excel to automatically fill down the formulas from G13, H264 and AC767 to the row number in cell B1 automatically. The result would be that G13 would be filled down to G10012, H264 would be filled down to H10012 and AC767 would be filled down to AC10012. I want Excel to do this automatically instead of me choosing the cells and manually filling down. Does anyone know how to accomplish this? Daniel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Down Automatically ?
Tom,
Thanks for your reply. I haven't written macros in Excel before but I think a macro is what is needed here. I think the macro should first clear all cells below G13, H264 and AC767 within each column. Then the macro should fill down from those cells to the row number which is in B1. Ideally, I'd like for as the value in B1 changes that the number of cells filled down to change. But I don't think I can do that. So I think the steps will have to be: 1) import the data which will determine a value for B1. 2) run the macro to clear any filled cells below G13, H264 and AC767 and then re-fill from G13, H264 and AC767 down to the row value in B1. The value in B1 can decrease as well as increase. The data imported into the spreadsheet is daily stock prices. Prices for a different stock can loaded and different time frames examined. Right now the data is manually added but later may come from a data feed. Sincerely, Daniel ---------------------------------------------------------------------------------------------------------- "Tom Ogilvy" wrote in message ... You could write a macro to do it. By automatically, do you mean when the value in B1 changes, then the cells would be filled down? How about if B1 decreases - would formulas be cleared. How does the data in the last row get added (so that B1 would change)? Manually? -- Regards, Tom Ogilvy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Down Automatically ?
Sub UpdateFormulas()
Range("G14:G65536").ClearContents Range("H265:H65536").ClearContents Range("AC768:AC65536").ClearContents Range("G14:G" & Range("B1").Value).Formula = Range("G14").Formula Range("H264:H" & Range("B1").Value).Formula = Range("H264").Formula Range("AC767:AC" & Range("B1").Value).Formula = Range("AC767").Formula End Sub -- Regards, Tom Ogilvy "Daniel Rascoe" wrote in message ... Tom, Thanks for your reply. I haven't written macros in Excel before but I think a macro is what is needed here. I think the macro should first clear all cells below G13, H264 and AC767 within each column. Then the macro should fill down from those cells to the row number which is in B1. Ideally, I'd like for as the value in B1 changes that the number of cells filled down to change. But I don't think I can do that. So I think the steps will have to be: 1) import the data which will determine a value for B1. 2) run the macro to clear any filled cells below G13, H264 and AC767 and then re-fill from G13, H264 and AC767 down to the row value in B1. The value in B1 can decrease as well as increase. The data imported into the spreadsheet is daily stock prices. Prices for a different stock can loaded and different time frames examined. Right now the data is manually added but later may come from a data feed. Sincerely, Daniel -------------------------------------------------------------------------- -------------------------------- "Tom Ogilvy" wrote in message ... You could write a macro to do it. By automatically, do you mean when the value in B1 changes, then the cells would be filled down? How about if B1 decreases - would formulas be cleared. How does the data in the last row get added (so that B1 would change)? Manually? -- Regards, Tom Ogilvy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Down Automatically ?
Tom,
You are good. It works very well. When filling down the G14 should be G13. Otherwise, perfect! Thanks a lot! Daniel "Tom Ogilvy" wrote in message ... Sub UpdateFormulas() Range("G14:G65536").ClearContents Range("H265:H65536").ClearContents Range("AC768:AC65536").ClearContents Range("G14:G" & Range("B1").Value).Formula = Range("G14").Formula Range("H264:H" & Range("B1").Value).Formula = Range("H264").Formula Range("AC767:AC" & Range("B1").Value).Formula = Range("AC767").Formula End Sub -- Regards, Tom Ogilvy "Daniel Rascoe" wrote in message ... Tom, Thanks for your reply. I haven't written macros in Excel before but I think a macro is what is needed here. I think the macro should first clear all cells below G13, H264 and AC767 within each column. Then the macro should fill down from those cells to the row number which is in B1. Ideally, I'd like for as the value in B1 changes that the number of cells filled down to change. But I don't think I can do that. So I think the steps will have to be: 1) import the data which will determine a value for B1. 2) run the macro to clear any filled cells below G13, H264 and AC767 and then re-fill from G13, H264 and AC767 down to the row value in B1. The value in B1 can decrease as well as increase. The data imported into the spreadsheet is daily stock prices. Prices for a different stock can loaded and different time frames examined. Right now the data is manually added but later may come from a data feed. Sincerely, Daniel -------------------------------------------------------------------------- -------------------------------- "Tom Ogilvy" wrote in message ... You could write a macro to do it. By automatically, do you mean when the value in B1 changes, then the cells would be filled down? How about if B1 decreases - would formulas be cleared. How does the data in the last row get added (so that B1 would change)? Manually? -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filling down to the end of a data set automatically | Excel Discussion (Misc queries) | |||
Automatically filling fields | Excel Discussion (Misc queries) | |||
Filling Data automatically | Excel Discussion (Misc queries) | |||
filling in data automatically | Excel Discussion (Misc queries) | |||
Automatically filling in data | Excel Discussion (Misc queries) |