Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range for autofill macro
I have a data sheet where I have to add a formula to the last 2 columns. The
number of rows changes with each new data sheet and can be several thousand rows. The sheet is called "JobLabour". The last column is Y and the data starts below the heading "Line Property" at "Y7" (this could change, although I have control over the report format). I enter a formula (from the macro code) , "=IF(RC[-13]=""wlpn"",80,IF(RC[-13]=""2alpn"",44,IF(RC[-13]=""1alpn"",36,IF(RC[-13]=""3alpn"",60,IF(RC[-13]=""4alpn"",71,80)))))" at cell Z8. I have the formula, "=RC[-8]*RC[-1]" in cell AA8. I need to copy this down to the last populated value in col "Y". Is there code that will do this automatically when new data is pasted into sheet "JobLabour"? I currently do this via a macro, but users often forget to run it and I have to set the number of rows to fill. -- Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range for autofill macro
Dim LastRow as long
with worksheets("JobLabour") Lastrow = .cells(.rows.count,"Y").end(xlup).row .range("AA8:AA" & lastrow).formular1c1 = "=RC[-8]*RC[-1]" end with ======= You could try toggling a setting: tools|Options|edit tab|Extend data range formats and formulas But I think I'd provide a macro that inserted the row and filled the formulas. David McRitchie shares some code: http://www.mvps.org/dmcritchie/excel/insrtrow.htm look for: InsertRowsAndFillFormulas Jim G wrote: I have a data sheet where I have to add a formula to the last 2 columns. The number of rows changes with each new data sheet and can be several thousand rows. The sheet is called "JobLabour". The last column is Y and the data starts below the heading "Line Property" at "Y7" (this could change, although I have control over the report format). I enter a formula (from the macro code) , "=IF(RC[-13]=""wlpn"",80,IF(RC[-13]=""2alpn"",44,IF(RC[-13]=""1alpn"",36,IF(RC[-13]=""3alpn"",60,IF(RC[-13]=""4alpn"",71,80)))))" at cell Z8. I have the formula, "=RC[-8]*RC[-1]" in cell AA8. I need to copy this down to the last populated value in col "Y". Is there code that will do this automatically when new data is pasted into sheet "JobLabour"? I currently do this via a macro, but users often forget to run it and I have to set the number of rows to fill. -- Jim -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic range for autofill macro
Thanks Dave, that's exactly what I needed. I've been able to adopt this to
similar situations. -- Jim "Dave Peterson" wrote: Dim LastRow as long with worksheets("JobLabour") Lastrow = .cells(.rows.count,"Y").end(xlup).row .range("AA8:AA" & lastrow).formular1c1 = "=RC[-8]*RC[-1]" end with ======= You could try toggling a setting: tools|Options|edit tab|Extend data range formats and formulas But I think I'd provide a macro that inserted the row and filled the formulas. David McRitchie shares some code: http://www.mvps.org/dmcritchie/excel/insrtrow.htm look for: InsertRowsAndFillFormulas Jim G wrote: I have a data sheet where I have to add a formula to the last 2 columns. The number of rows changes with each new data sheet and can be several thousand rows. The sheet is called "JobLabour". The last column is Y and the data starts below the heading "Line Property" at "Y7" (this could change, although I have control over the report format). I enter a formula (from the macro code) , "=IF(RC[-13]=""wlpn"",80,IF(RC[-13]=""2alpn"",44,IF(RC[-13]=""1alpn"",36,IF(RC[-13]=""3alpn"",60,IF(RC[-13]=""4alpn"",71,80)))))" at cell Z8. I have the formula, "=RC[-8]*RC[-1]" in cell AA8. I need to copy this down to the last populated value in col "Y". Is there code that will do this automatically when new data is pasted into sheet "JobLabour"? I currently do this via a macro, but users often forget to run it and I have to set the number of rows to fill. -- Jim -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a dynamic range in a macro | Excel Worksheet Functions | |||
'Autofill' macro for range of checkbox cell links | Excel Discussion (Misc queries) | |||
autofill a column alongside a range - how ? | Excel Discussion (Misc queries) | |||
How to drag and autofill a non consecutive range of cells? | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |