Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill formula to last row with data when rows will be dynamic
I am writing a macro. It's more complicated than I originally thought. What I
need to do is autofill a formula in a column to the last row with data. The problem is the rows are dynamic. They start in a different place each time and end in a different place. The range size will vary every time the macro is used. Once the formula has calculated the neccessary numbers I want to copy and paste the information back to another column. My problem is telling excel which function I want to perform when the calculations will be made on a moving target. I have the macro working to the first cell where the formula is entered. Now I need to copy the formula to the last row that contains information the formula can use. Please help. I have posted this previously but it was days ago and still no answer. Here's a sample of my macro. This is only a small part. Sheets("BOMSized").Select Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut ActiveCell.Offset(0, 1).Select ActiveSheet.Paste ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12" -- A Waller |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill formula to last row with data when rows will be dynamic
A,
Replace ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12" with Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _ .FormulaR1C1 = "=RC[-1]*RC[6]/12" HTH, Bernie MS Excel MVP "A Waller" wrote in message ... I am writing a macro. It's more complicated than I originally thought. What I need to do is autofill a formula in a column to the last row with data. The problem is the rows are dynamic. They start in a different place each time and end in a different place. The range size will vary every time the macro is used. Once the formula has calculated the neccessary numbers I want to copy and paste the information back to another column. My problem is telling excel which function I want to perform when the calculations will be made on a moving target. I have the macro working to the first cell where the formula is entered. Now I need to copy the formula to the last row that contains information the formula can use. Please help. I have posted this previously but it was days ago and still no answer. Here's a sample of my macro. This is only a small part. Sheets("BOMSized").Select Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut ActiveCell.Offset(0, 1).Select ActiveSheet.Paste ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12" -- A Waller |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill formula to last row with data when rows will be dynam
Thank you so much! That worked like a charm!
"Bernie Deitrick" wrote: A, Replace ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12" with Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _ .FormulaR1C1 = "=RC[-1]*RC[6]/12" HTH, Bernie MS Excel MVP "A Waller" wrote in message ... I am writing a macro. It's more complicated than I originally thought. What I need to do is autofill a formula in a column to the last row with data. The problem is the rows are dynamic. They start in a different place each time and end in a different place. The range size will vary every time the macro is used. Once the formula has calculated the neccessary numbers I want to copy and paste the information back to another column. My problem is telling excel which function I want to perform when the calculations will be made on a moving target. I have the macro working to the first cell where the formula is entered. Now I need to copy the formula to the last row that contains information the formula can use. Please help. I have posted this previously but it was days ago and still no answer. Here's a sample of my macro. This is only a small part. Sheets("BOMSized").Select Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Selection.End(xlToRight).Select ActiveCell.Offset(0, 1).Select Range(Selection, Selection.End(xlDown)).Select Selection.Cut ActiveCell.Offset(0, 1).Select ActiveSheet.Paste ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12" -- A Waller |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill formula to last row with data when rows will be dynam
You're welcome. Charmed code is a speciality of this newsgroup ;-).
HTH, Bernie MS Excel MVP Thank you so much! That worked like a charm! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Autofill | Excel Discussion (Misc queries) | |||
Formula to autofill with an evenly spaced increment of rows? | Excel Discussion (Misc queries) | |||
making autofill range dynamic | Excel Programming | |||
autofill with dynamic range | Excel Programming | |||
autofill with dynamic range again | Excel Programming |