Sum cell formula

[Exact]

Mac Office 365

Hi, this is a question for understanding.

What I want and what works is this:
Range("B1").Select
Range("B1").Formula = "=SUM(" & Range("B2", Range("B2").End(xlDown)).Address & ")"

What I started with and works is this:
Range("B1").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[4],RC[-1],C[6])"
ActiveCell.Formula = "=SUM(" & Range(ActiveCell.Offset(1), ActiveCell.End(xlDown)).Address & ")"••••ˇˇˇˇ
As far as I can tell In this context the: ActiveCell.FormulaR1C1 = "=SUMIF(C[4],RC[-1],C[6])" doesn't do anything

But just taking out that line this does not work:
Range("B1").Select
ActiveCell.Formula = "=SUM(" & Range(ActiveCell.Offset(1), ActiveCell.End(xlDown)).Address & ")"••••ˇˇˇˇ

Can anyone help me understand why a line that uses a sumif with relative cell references is required to make the ActiveCell scenario work???

 

[StephenCrump]

It's not correct that the SUMIF formula "doesn't do anything". That line puts the formula =SUMIF(F:F,A1,H:H) into B1. The next code line overwrites it with a different formula.

I'm not sure why you say the third code snippet "does not work". It's identical to the second snippet and works just the same.

Incidentally, you don't need to do use all these .Select's. You could do it like this:

Range("B1").Formula = "=SUM(B2:B" & Range("B2").End(xlDown).Row & ")"

 

[Alex Blakenburg]

We are doing a lot of guessing here. You really need to show us what your sheet looks like and what doesn't work means.
My guess is that you have a block of data under the ActiveCell that you want to add up and are using xlDown to determine the last cell in the block.
When B1 is blank xlDown is stopping at B2 instead of the end of the block.
If that is the case try:

Range("B1").Select
ActiveCell.Formula = "=SUM(" & Range(ActiveCell.Offset(1), ActiveCell.Offset(1).End(xlDown)).Address & ")"

If that works then all the first formula was doing was making sure that B1 wasn't blank so that xlDown goes to the end of the block.
Note: Stephen starts his xlDown from B2 while you are starting from B1.

 

[Exact]

Mac Office 365
Hi, this is in a new spreadsheet with just this one simple macro.

From the code C & D should produce the same result but as you can see it does not and I don't know why??

Sub makesnosense()

Range("B1").Formula = "=SUM(" & Range("B2", Range("B2").End(xlDown)).Address & ")"

Range("C1").Select
ActiveCell.Formula = "=SUM(" & Range(ActiveCell.Offset(1), ActiveCell.End(xlDown)).Address & ")"

Range("D1").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[4],RC[-1],C[6])"
ActiveCell.Formula = "=SUM(" & Range(ActiveCell.Offset(1), ActiveCell.End(xlDown)).Address & ")"

Range("E1").Formula = "=SUM(E2:E" & Range("E2").End(xlDown).Row & ")"

End Sub

 

[StephenCrump]

This is exactly the point that Alex made in Post #3.

If ActiveCell is C1 and there's nothing already in that cell, and C2 is populated, then .End(xlDown) will take you to C2, not C6. (Just the same as manually selecting C1 and hitting CTRL and down arrow).

Instead, you could use:

Range("C1").Select
ActiveCell.Formula = "=SUM(" & Range(ActiveCell.Offset(1), ActiveCell.Offset(1).End(xlDown)).Address & ")"

Better still, avoid using .Select and ActiveCell, as in Post #2.

And always be careful using .End(xlDown) (or xlToRight) as they may produce funny results if the cells aren't populated the way you are expecting them to be!

 

[Exact]

Thank you all for your help.