SUM with variable range
 

I am not familiar with the DataSubTotals menu, and don't know how it
works...

However, I came up with a solution that works. I basically create a
string with the formula I want to insert, like this:

(selection is column A)

a = 0
Str1 = "=SUM(R[-"
For Each b In selection.Cells
If Left(b.Value, 1) = " " Then
... here I copy the easy "=B1*C1" formula
Else
Str1 = Str1 + Trim(Str(a)) + "]C:R[-1]C)"
b.Offset(0, 3).FormulaR1C1 = Str1
a = -1
Str1 = "=SUM(R[-"
End If
a = a + 1
Next

Every time I put the sum formula for the cells above, I restart the
Str1 and a variables. It works beautifully!

Thanks anyway!
Jose


On Jun 13, 12:59 am, "NickHK" wrote:
That should be :
Maybe add a column for "Family", then look at DataSubTotals menu.

NickHK

"NickHK" wrote in message

...

SUM with variable range
 

Jose,
If you tried it you would see how it works.
But if you already have a solution, well go with that.

NickHK

"Jose" wrote in message
ups.com...
I am not familiar with the DataSubTotals menu, and don't know how it
works...

However, I came up with a solution that works. I basically create a
string with the formula I want to insert, like this:

(selection is column A)

a = 0
Str1 = "=SUM(R[-"
For Each b In selection.Cells
If Left(b.Value, 1) = " " Then
... here I copy the easy "=B1*C1" formula
Else
Str1 = Str1 + Trim(Str(a)) + "]C:R[-1]C)"
b.Offset(0, 3).FormulaR1C1 = Str1
a = -1
Str1 = "=SUM(R[-"
End If
a = a + 1
Next

Every time I put the sum formula for the cells above, I restart the
Str1 and a variables. It works beautifully!

Thanks anyway!
Jose


On Jun 13, 12:59 am, "NickHK" wrote:
That should be :
Maybe add a column for "Family", then look at DataSubTotals menu.

NickHK

"NickHK" wrote in message

...

SUM with variable range
 

I did try it, but couldn't really understand how it works. I tried
later again, and now I see how it works, and how it can be useful in
the future. But for this case, it messes up the table. The other
solution would be a better fit.

Thanks anyway, I learned another good function in Excel!

Jose


On Jun 13, 10:58 pm, "NickHK" wrote:
Jose,
If you tried it you would see how it works.
But if you already have a solution, well go with that.

NickHK

"Jose" wrote in message

ups.com...



I am not familiar with the DataSubTotals menu, and don't know how it
works...

However, I came up with a solution that works. I basically create a
string with the formula I want to insert, like this:

(selection is column A)

a = 0
Str1 = "=SUM(R[-"
For Each b In selection.Cells
If Left(b.Value, 1) = " " Then
... here I copy the easy "=B1*C1" formula
Else
Str1 = Str1 + Trim(Str(a)) + "]C:R[-1]C)"
b.Offset(0, 3).FormulaR1C1 = Str1
a = -1
Str1 = "=SUM(R[-"
End If
a = a + 1
Next

Every time I put the sum formula for the cells above, I restart the
Str1 and a variables. It works beautifully!

Thanks anyway!
Jose

On Jun 13, 12:59 am, "NickHK" wrote:
That should be :
Maybe add a column for "Family", then look at DataSubTotals menu.

NickHK

"NickHK" wrote in message

...- Hide quoted text -

- Show quoted text -