| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Formula Problem
I have a range BFP (A11:A33) that has 3 columns of formulas (C, D, E) that
use SumProduct that refers to cells on the current sheet and cells on another sheet. This is the formula in Column C: (SUMPRODUCT(O78:Z78,'2006 Actual'!C10:N10)/1000). Column D & E use same formula, but refer to other sheets. When I insert a row, I need to update the formulas starting with the inserted row down to the last row to update the formulas for the other sheets. But since the last row address in the BFP Range changes, I cannot figure out how to get the row address to copy the formula down to. I'm probably making this too hard, but here is the macro I started, before I came up with the range name. Sheets("Growth Rates").Activate Rows(RowNum).Select Selection.Insert Shift:=xlDown Range("A" & RowNumM1 & ":" & "H" & RowNumM1).Select Selection.Copy Range("A" & RowNum).Select ActiveSheet.Paste Application.CutCopyMode = False Range("C" & RowNumM1 & ":" & "E" & RowNumM1).Select Selection.AutoFill Destination:=Range("C" & RowNumM1 & ":" & "E" & RowNum), Type:=xlFillDefault Range("C" & RowNumM1 & ":" & "E" & RowNum).Select I used an input box to get the RowNum where the user wants to insert the new row. RowNumM1 is the RowNum - 1 I'm trying to get the RowNum of the last row in the range (I know I need another variable name) for the Selection.AutoFill Destination or maybe there is a more simple way. Any help would be appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Formula Problem
OK...I've got the named range...now how do I update the formulas from the row
inserted to the last row in the range? Thanks! "Don Guillett" wrote: Why not just use a defined name to self adjust the range insertnamedefinename it as desired "myrng"in the refers to box =offset($a$1,1,0,counta($a:$a)-1,6) look in the help index for OFFSET. Modify to suit BTW. In your macros, selections are RARELY necessary or desirable -- Don Guillett SalesAid Software "David" wrote in message ... I have a range BFP (A11:A33) that has 3 columns of formulas (C, D, E) that use SumProduct that refers to cells on the current sheet and cells on another sheet. This is the formula in Column C: (SUMPRODUCT(O78:Z78,'2006 Actual'!C10:N10)/1000). Column D & E use same formula, but refer to other sheets. When I insert a row, I need to update the formulas starting with the inserted row down to the last row to update the formulas for the other sheets. But since the last row address in the BFP Range changes, I cannot figure out how to get the row address to copy the formula down to. I'm probably making this too hard, but here is the macro I started, before I came up with the range name. Sheets("Growth Rates").Activate Rows(RowNum).Select Selection.Insert Shift:=xlDown Range("A" & RowNumM1 & ":" & "H" & RowNumM1).Select Selection.Copy Range("A" & RowNum).Select ActiveSheet.Paste Application.CutCopyMode = False Range("C" & RowNumM1 & ":" & "E" & RowNumM1).Select Selection.AutoFill Destination:=Range("C" & RowNumM1 & ":" & "E" & RowNum), Type:=xlFillDefault Range("C" & RowNumM1 & ":" & "E" & RowNum).Select I used an input box to get the RowNum where the user wants to insert the new row. RowNumM1 is the RowNum - 1 I'm trying to get the RowNum of the last row in the range (I know I need another variable name) for the Selection.AutoFill Destination or maybe there is a more simple way. Any help would be appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Formula Problem
I guess I misunderstood your request. If you are saying that you want to
create new formulas from ONLY the inserted row then please provide your complete code and examples of before and after formulas for several rows or send me a file with a clear explanation. -- Don Guillett SalesAid Software "David" wrote in message ... OK...I've got the named range...now how do I update the formulas from the row inserted to the last row in the range? Thanks! "Don Guillett" wrote: Why not just use a defined name to self adjust the range insertnamedefinename it as desired "myrng"in the refers to box =offset($a$1,1,0,counta($a:$a)-1,6) look in the help index for OFFSET. Modify to suit BTW. In your macros, selections are RARELY necessary or desirable -- Don Guillett SalesAid Software "David" wrote in message ... I have a range BFP (A11:A33) that has 3 columns of formulas (C, D, E) that use SumProduct that refers to cells on the current sheet and cells on another sheet. This is the formula in Column C: (SUMPRODUCT(O78:Z78,'2006 Actual'!C10:N10)/1000). Column D & E use same formula, but refer to other sheets. When I insert a row, I need to update the formulas starting with the inserted row down to the last row to update the formulas for the other sheets. But since the last row address in the BFP Range changes, I cannot figure out how to get the row address to copy the formula down to. I'm probably making this too hard, but here is the macro I started, before I came up with the range name. Sheets("Growth Rates").Activate Rows(RowNum).Select Selection.Insert Shift:=xlDown Range("A" & RowNumM1 & ":" & "H" & RowNumM1).Select Selection.Copy Range("A" & RowNum).Select ActiveSheet.Paste Application.CutCopyMode = False Range("C" & RowNumM1 & ":" & "E" & RowNumM1).Select Selection.AutoFill Destination:=Range("C" & RowNumM1 & ":" & "E" & RowNum), Type:=xlFillDefault Range("C" & RowNumM1 & ":" & "E" & RowNum).Select I used an input box to get the RowNum where the user wants to insert the new row. RowNumM1 is the RowNum - 1 I'm trying to get the RowNum of the last row in the range (I know I need another variable name) for the Selection.AutoFill Destination or maybe there is a more simple way. Any help would be appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Formula Problem
When I insert a row, say row 18 in the range, the formulas in columns C, D, E
look like this: Row 17 SUMPRODUCT(O78:Z78,'2006 Actual'!C10:N10)/1000 (Right) Row 18 SUMPRODUCT(O79:Z79,'2006 Actual'!C11:N11)/1000 (Right) Row 19 SUMPRODUCT(O79:Z79,'2006 Actual'!C11:N11)/1000 (Wrong) to end of range is off by the one row. Row 19 and following should increase by one row so 19 would be O80/C12:N12 I recorded a macro that selected the row ABOVE the inserted row and dragged down to the end of the range to update all the formulas after the inserted row. That's why the "select" is in the code. Thanks! "Don Guillett" wrote: I guess I misunderstood your request. If you are saying that you want to create new formulas from ONLY the inserted row then please provide your complete code and examples of before and after formulas for several rows or send me a file with a clear explanation. -- Don Guillett SalesAid Software "David" wrote in message ... OK...I've got the named range...now how do I update the formulas from the row inserted to the last row in the range? Thanks! "Don Guillett" wrote: Why not just use a defined name to self adjust the range insertnamedefinename it as desired "myrng"in the refers to box =offset($a$1,1,0,counta($a:$a)-1,6) look in the help index for OFFSET. Modify to suit BTW. In your macros, selections are RARELY necessary or desirable -- Don Guillett SalesAid Software "David" wrote in message ... I have a range BFP (A11:A33) that has 3 columns of formulas (C, D, E) that use SumProduct that refers to cells on the current sheet and cells on another sheet. This is the formula in Column C: (SUMPRODUCT(O78:Z78,'2006 Actual'!C10:N10)/1000). Column D & E use same formula, but refer to other sheets. When I insert a row, I need to update the formulas starting with the inserted row down to the last row to update the formulas for the other sheets. But since the last row address in the BFP Range changes, I cannot figure out how to get the row address to copy the formula down to. I'm probably making this too hard, but here is the macro I started, before I came up with the range name. Sheets("Growth Rates").Activate Rows(RowNum).Select Selection.Insert Shift:=xlDown Range("A" & RowNumM1 & ":" & "H" & RowNumM1).Select Selection.Copy Range("A" & RowNum).Select ActiveSheet.Paste Application.CutCopyMode = False Range("C" & RowNumM1 & ":" & "E" & RowNumM1).Select Selection.AutoFill Destination:=Range("C" & RowNumM1 & ":" & "E" & RowNum), Type:=xlFillDefault Range("C" & RowNumM1 & ":" & "E" & RowNum).Select I used an input box to get the RowNum where the user wants to insert the new row. RowNumM1 is the RowNum - 1 I'm trying to get the RowNum of the last row in the range (I know I need another variable name) for the Selection.AutoFill Destination or maybe there is a more simple way. Any help would be appreciated. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Formula Problem
I used your code to get the last row in the range, added 1 to it, and then
used the select autofill range to the last row + 1 and it works fine now. Don't spend any more time on it. Thanks! "Don Guillett" wrote: I guess I misunderstood your request. If you are saying that you want to create new formulas from ONLY the inserted row then please provide your complete code and examples of before and after formulas for several rows or send me a file with a clear explanation. -- Don Guillett SalesAid Software "David" wrote in message ... OK...I've got the named range...now how do I update the formulas from the row inserted to the last row in the range? Thanks! "Don Guillett" wrote: Why not just use a defined name to self adjust the range insertnamedefinename it as desired "myrng"in the refers to box =offset($a$1,1,0,counta($a:$a)-1,6) look in the help index for OFFSET. Modify to suit BTW. In your macros, selections are RARELY necessary or desirable -- Don Guillett SalesAid Software "David" wrote in message ... I have a range BFP (A11:A33) that has 3 columns of formulas (C, D, E) that use SumProduct that refers to cells on the current sheet and cells on another sheet. This is the formula in Column C: (SUMPRODUCT(O78:Z78,'2006 Actual'!C10:N10)/1000). Column D & E use same formula, but refer to other sheets. When I insert a row, I need to update the formulas starting with the inserted row down to the last row to update the formulas for the other sheets. But since the last row address in the BFP Range changes, I cannot figure out how to get the row address to copy the formula down to. I'm probably making this too hard, but here is the macro I started, before I came up with the range name. Sheets("Growth Rates").Activate Rows(RowNum).Select Selection.Insert Shift:=xlDown Range("A" & RowNumM1 & ":" & "H" & RowNumM1).Select Selection.Copy Range("A" & RowNum).Select ActiveSheet.Paste Application.CutCopyMode = False Range("C" & RowNumM1 & ":" & "E" & RowNumM1).Select Selection.AutoFill Destination:=Range("C" & RowNumM1 & ":" & "E" & RowNum), Type:=xlFillDefault Range("C" & RowNumM1 & ":" & "E" & RowNum).Select I used an input box to get the RowNum where the user wants to insert the new row. RowNumM1 is the RowNum - 1 I'm trying to get the RowNum of the last row in the range (I know I need another variable name) for the Selection.AutoFill Destination or maybe there is a more simple way. Any help would be appreciated. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Range Formula Problem
Nevertheless, perhaps this covers your original question from anwhere in the
workbook with NO selections. Sub fixformulasdown() rowsnum = 20 With Sheets("sheet6") .Rows(rowsnum).Insert lr = .Cells(Rows.Count, "e").End(xlUp).Row .Rows(rowsnum - 1).Copy .Rows(rowsnum & ":" & lr) End With End Sub -- Don Guillett SalesAid Software "David" wrote in message ... I used your code to get the last row in the range, added 1 to it, and then used the select autofill range to the last row + 1 and it works fine now. Don't spend any more time on it. Thanks! "Don Guillett" wrote: I guess I misunderstood your request. If you are saying that you want to create new formulas from ONLY the inserted row then please provide your complete code and examples of before and after formulas for several rows or send me a file with a clear explanation. -- Don Guillett SalesAid Software "David" wrote in message ... OK...I've got the named range...now how do I update the formulas from the row inserted to the last row in the range? Thanks! "Don Guillett" wrote: Why not just use a defined name to self adjust the range insertnamedefinename it as desired "myrng"in the refers to box =offset($a$1,1,0,counta($a:$a)-1,6) look in the help index for OFFSET. Modify to suit BTW. In your macros, selections are RARELY necessary or desirable -- Don Guillett SalesAid Software "David" wrote in message ... I have a range BFP (A11:A33) that has 3 columns of formulas (C, D, E) that use SumProduct that refers to cells on the current sheet and cells on another sheet. This is the formula in Column C: (SUMPRODUCT(O78:Z78,'2006 Actual'!C10:N10)/1000). Column D & E use same formula, but refer to other sheets. When I insert a row, I need to update the formulas starting with the inserted row down to the last row to update the formulas for the other sheets. But since the last row address in the BFP Range changes, I cannot figure out how to get the row address to copy the formula down to. I'm probably making this too hard, but here is the macro I started, before I came up with the range name. Sheets("Growth Rates").Activate Rows(RowNum).Select Selection.Insert Shift:=xlDown Range("A" & RowNumM1 & ":" & "H" & RowNumM1).Select Selection.Copy Range("A" & RowNum).Select ActiveSheet.Paste Application.CutCopyMode = False Range("C" & RowNumM1 & ":" & "E" & RowNumM1).Select Selection.AutoFill Destination:=Range("C" & RowNumM1 & ":" & "E" & RowNum), Type:=xlFillDefault Range("C" & RowNumM1 & ":" & "E" & RowNum).Select I used an input box to get the RowNum where the user wants to insert the new row. RowNumM1 is the RowNum - 1 I'm trying to get the RowNum of the last row in the range (I know I need another variable name) for the Selection.AutoFill Destination or maybe there is a more simple way. Any help would be appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
| formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
| Problem copying formula to range of cells | Setting up and Configuration of Excel | |||
| cell vs range problem with formula | Excel Programming | |||
| Reference range in formula problem | Excel Programming | |||
Linear Mode
