insert new row and copy formula

  • Thread starter Thread starter bumiblumi
  • Start date Start date
B

bumiblumi

Hi all,

I have a problem with a macro to add a new row and copy formulas.
I use the same macro but in different workbooks, the result on formulas is
different.


for 1st time, result is the same for both workbooks:
[C6] =SUMIF(CourseCodes,Enrolment!B6,CourseFees)
[C7] =SUMIF(CourseCodes,Enrolment!B7,CourseFees)
[C8] =SUM(C$6:C7)

but when trying further, there is a difference:

in one workbook:
[C6] =SUMIF(CourseCodes,Enrolment!B6,CourseFees)
[C7] =SUMIF(CourseCodes,Enrolment!B7,CourseFees)
[C8] =SUM(C$6:C7)
[C9] =SUM(C$6:C8)

in second workbook:
[C6] =SUMIF(CourseCodes,Enrolment!B6,CourseCost)
[C7] =SUMIF(CourseCodes,Enrolment!B7,CourseCost)
[C8] =SUMIF(CourseCodes,Enrolment!B8,CourseCost)
[C9] =SUM(C$6:C8)

I need the effect as in the second workbook, but I don't know how to change
the first one. I can't understand what makes the difference, can anyone help
me please?

btw, here is the macro:
Sub Macro3()
'
' Macro3 Macro
'

'
Range("B7").Select
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Range("C6").Select
Selection.AutoFill Destination:=Range("C6:C7"), Type:=xlFillDefault
Range("C6:C7").Select
Range("C8").Select
ActiveCell.FormulaR1C1 = "=SUM(R6C:R[-1]C)"
Range("C9").Select
End Sub
 
Try these:

Sub AAAA()
'always starts at C6
Dim rng As Range
Set rng = Range("C6").End(xlDown)
rng.EntireRow.Insert
rng.Offset(-2, 0).Copy Destination:=rng.Offset(-1, 0)
rng.FormulaR1C1 = "=SUM(R6C:R[-1]C)"
Set rng = Nothing
End Sub

Sub BBBB()
'must select top cell of range before running
Dim rng1 As Range, rng2 As Range
Set rng1 = ActiveCell
Set rng2 = rng1.End(xlDown)
rng2.EntireRow.Insert
rng2.Offset(-2, 0).Copy Destination:=rng2.Offset(-1, 0)
rng2.FormulaR1C1 = "=SUM(R" & rng1.Row & "C:R[-1]C)"
Set rng1 = Nothing
Set rng2 = Nothing
End Sub

The first version always starts in C6. There must be something in C6 and (at
least) C7.

The second version works almost anywhere, but you have to select the top
cell of the range before running it. There must be something in the selected
cell and (at least) the cell below it.

Hope this helps,

Hutch
 
Back
Top