VBA - Change sum cell

[ChrisFoster]

Hi all,

I have some code, as below which does a sum of column L. I have recently had a new column added into a report and now need the below code to sum column M instead of L.
I expect this is an easy thing to change but I don't know which bit relates to the column.

    ActiveCell.FormulaR1C1 = _
        "=SUMIF('Arrears Breakdown'!C[3],RC[-2],'Arrears Breakdown'!C[9])"

Please can someone tell me how to sum column M instead of L?

Cheers

Chris

 

[Fluff]

How about

    ActiveCell.FormulaR1C1 = _
        "=SUMIF('Arrears Breakdown'!C[3],RC[-2],'Arrears Breakdown'!C13)"

 

[ChrisFoster]

How about

    ActiveCell.FormulaR1C1 = _
        "=SUMIF('Arrears Breakdown'!C[3],RC[-2],'Arrears Breakdown'!C13)"

Yes and No. You've helped me sort it though.
My active cell was C2 which I didn't mention in my first post, so I've used the below which has worked.

    ActiveCell.FormulaR1C1 = _
        "=SUMIF('Arrears Breakdown'!C[3],RC[-2],'Arrears Breakdown'!C10)"

Thanks for your help,

Chris

 

[Fluff]

That is going to sum col J not col M, regardless of what cell the formula is in.

 

[ChrisFoster]

That is going to sum col J not col M, regardless of what cell the formula is in.

It definitely sums column M. It inserts the below formula.

=SUMIF('Arrears Breakdown'!D:D,E2,'Arrears Breakdown'!M:M)

 

[Fluff]

If the formula you posted is in C2 it will give you

=SUMIF('Arrears Breakdown'!F:F,A2,'Arrears Breakdown'!$J:$J)

Regardless of what cell the formula is in it will sum col J as

'Arrears Breakdown'!C10

is an absolute address to col 10 which is J

 

[ChrisFoster]

If the formula you posted is in C2 it will give you

=SUMIF('Arrears Breakdown'!F:F,A2,'Arrears Breakdown'!$J:$J)

Regardless of what cell the formula is in it will sum col J as

'Arrears Breakdown'!C10

is an absolute address to col 10 which is J

Ah, sorry. I actually used;

    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF('Arrears Breakdown'!C[3],RC[-2],'Arrears Breakdown'!C[10])"

Do the brackets make a difference in C[10]?

 

[Fluff]

Yes they make a very big difference using C10 will return col J regardless regardless of which cell the formula is in, whereas C[10] will return the 10th to the right of the formula (not counting the column with the formula)

 

[ChrisFoster]

Yes they make a very big difference using C10 will return col J regardless regardless of which cell the formula is in, whereas C[10] will return the 10th to the right of the formula (not counting the column with the formula)

Thanks alot for taking the time to help me with this, it's much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.