Range Formula Problem

[David]

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.

 

[Don Guillett]

Why not just use a defined name to self adjust the range
insert>name>define>name 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

 

[David]

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]

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.

 

[David]

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!

 

[David]

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]

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