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. |
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 |