Variable SUM range
 

I am stuck with VBA syntax.


A1 has value of 123

A2 is blank

A3 also has value of 123

In A4 need a SUM range of A3 only

If A1:A3 all have values then need SUM range of A1:A3

This is what I have now but does not do the variable trick.

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1,0).End(xlUp))

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"


Gord

Variable SUM range
 

Have come up with this which does the job for now but there has to be
a better method than for/each

ActiveCell is A4

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1, 0).End(xlUp))

MsgBox rng3.Address 'returns A1:A3

For Each cell In rng3
If cell.Value = "" Then
Set rng3 = ActiveCell.Offset(-1, 0)

MsgBox rng3.Address 'returns A3

End If
Next

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"


Gord


On Thu, 24 Nov 2011 15:20:53 -0800, Gord Dibben
wrote:

I am stuck with VBA syntax.


A1 has value of 123

A2 is blank

A3 also has value of 123

In A4 need a SUM range of A3 only

If A1:A3 all have values then need SUM range of A1:A3

This is what I have now but does not do the variable trick.

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1,0).End(xlUp))

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"


Gord

Variable SUM range
 

Gord,
Maybe...
ActiveCell.Formula = "=A3+MIN(LEN(A1)<0,LEN(A2)<0)*(A1+A2)"

Also, Isabelle ought to be along any minute with something better. <g
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware



"Gord Dibben"

wrote in message
...
I am stuck with VBA syntax.
A1 has value of 123

A2 is blank

A3 also has value of 123

In A4 need a SUM range of A3 only

If A1:A3 all have values then need SUM range of A1:A3

This is what I have now but does not do the variable trick.

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1,0).End(xlUp))

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"

Gord

Variable SUM range
 

Thanks Jim

The A1:A3 range is simply an example

I cannot have hard-coded cells.

The Total value(SUM) can take place anywhere in a column.

There could be as many as 5 or 6 contiguous value cells or only one.

No other scenario.

See my other post for a method which does work but uses for/each.

I'll wait for Isabelle or Don but I know Don is watching the "horns"
game.

No rush.


Gord



On Thu, 24 Nov 2011 16:32:31 -0800, "Jim Cone"
wrote:

Gord,
Maybe...
ActiveCell.Formula = "=A3+MIN(LEN(A1)<0,LEN(A2)<0)*(A1+A2)"

Also, Isabelle ought to be along any minute with something better. <g
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware



"Gord Dibben"

wrote in message
.. .
I am stuck with VBA syntax.
A1 has value of 123

A2 is blank

A3 also has value of 123

In A4 need a SUM range of A3 only

If A1:A3 all have values then need SUM range of A1:A3

This is what I have now but does not do the variable trick.

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1,0).End(xlUp))

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"

Gord

Variable SUM range
 

if you insist ;-)

Set rng3 = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp))
ActiveCell.FormulaArray = _
"=SUM(INDIRECT(""A""&MAX(MIN(IF(" & rng3.Address & "="""",ROW(" & rng3.Address & "))),1)&"":" & rng3(3).Address & """))"

or

rng = "A" & Evaluate("MAX(MIN(IF(" & rng3.Address & "="""",ROW(" & rng3.Address & "))),1)") & ":" & rng3(3).Address
ActiveCell.Formula = "=Sum(" & rng & ")"


@+
--
isabelle


Le 2011-11-24 19:32, Jim Cone a écrit :
Gord,
Also, Isabelle ought to be along any minute with something better.<g

Variable SUM range
 

to be more general one could replace the "1" in the formula by rng3(1).row

--
isabelle

Variable SUM range
 

Thanks Isabelle.

I'll work on these.


Gord

On Thu, 24 Nov 2011 21:32:23 -0500, isabelle wrote:

if you insist ;-)

Set rng3 = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp))
ActiveCell.FormulaArray = _
"=SUM(INDIRECT(""A""&MAX(MIN(IF(" & rng3.Address & "="""",ROW(" & rng3.Address & "))),1)&"":" & rng3(3).Address & """))"

or

rng = "A" & Evaluate("MAX(MIN(IF(" & rng3.Address & "="""",ROW(" & rng3.Address & "))),1)") & ":" & rng3(3).Address
ActiveCell.Formula = "=Sum(" & rng & ")"


@+

Variable SUM range
 

or +more général

ActiveCell.FormulaArray = _
"=SUM(INDIRECT(""A""&MAX(MIN(IF(" & rng3.Address & "="""",ROW(" & rng3.Address & ")))," & rng3.Row & ")&"":" & rng3(rng3.Count).Address & """))"


@+
--
isabelle

Variable SUM range
 

OK, a couple more ...
'---
Sub PlusMore()
Dim rng3 As Range
Dim rngCnt As Long

Set rng3 = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp))
rngCnt = rng3.Cells.Count
If Application.WorksheetFunction.CountA(rng3) < rngCnt Then
Set rng3 = rng3(rngCnt)
End If
ActiveCell.Formula = "=Sum(" & rng3.Address & ")"
End Sub
'---

'Almost the same as your original code...
Sub PlusEvenMore()
Dim rng3 As Range
Dim N As Long
Dim vArr As Variant

Set rng3 = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp))
vArr = rng3.Value2
For N = LBound(vArr) To UBound(vArr)
If Len(vArr(N, 1)) < 1 Then
Set rng3 = ActiveCell.Offset(-1, 0)
Exit For
End If
Next
ActiveCell.Formula = "=Sum(" & rng3.Address & ")"
End Sub
'---
Jim Cone




"Gord Dibben"
wrote in message
...
Thanks Jim

The A1:A3 range is simply an example

I cannot have hard-coded cells.

The Total value(SUM) can take place anywhere in a column.

There could be as many as 5 or 6 contiguous value cells or only one.

No other scenario.

See my other post for a method which does work but uses for/each.

I'll wait for Isabelle or Don but I know Don is watching the "horns"
game.

No rush.
Gord



On Thu, 24 Nov 2011 16:32:31 -0800, "Jim Cone"
wrote:
Gord,
Maybe...
ActiveCell.Formula = "=A3+MIN(LEN(A1)<0,LEN(A2)<0)*(A1+A2)"

Also, Isabelle ought to be along any minute with something better. <g
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware




"Gord Dibben"

wrote in message
. ..
I am stuck with VBA syntax.
A1 has value of 123

A2 is blank

A3 also has value of 123

In A4 need a SUM range of A3 only

If A1:A3 all have values then need SUM range of A1:A3

This is what I have now but does not do the variable trick.

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1,0).End(xlUp))

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"

Gord

Variable SUM range
 

Thanks Jim

I earlier had worked on a routine similar to your PlusMore using
Counta but did not quite get the syntax right.

You nailed it.

Just what I was looking for..........no for each/next involved.

There will be no editing of the values after initial entry so
I decided to reduce the many Total formulas with this

ActiveCell.Value = WorksheetFunction.Sum(rng3)


Gord



On Thu, 24 Nov 2011 19:40:50 -0800, "Jim Cone"
wrote:

OK, a couple more ...
'---
Sub PlusMore()
Dim rng3 As Range
Dim rngCnt As Long

Set rng3 = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp))
rngCnt = rng3.Cells.Count
If Application.WorksheetFunction.CountA(rng3) < rngCnt Then
Set rng3 = rng3(rngCnt)
End If
ActiveCell.Formula = "=Sum(" & rng3.Address & ")"
End Sub
'---

'Almost the same as your original code...
Sub PlusEvenMore()
Dim rng3 As Range
Dim N As Long
Dim vArr As Variant

Set rng3 = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp))
vArr = rng3.Value2
For N = LBound(vArr) To UBound(vArr)
If Len(vArr(N, 1)) < 1 Then
Set rng3 = ActiveCell.Offset(-1, 0)
Exit For
End If
Next
ActiveCell.Formula = "=Sum(" & rng3.Address & ")"
End Sub
'---
Jim Cone




"Gord Dibben"
wrote in message
.. .
Thanks Jim

The A1:A3 range is simply an example

I cannot have hard-coded cells.

The Total value(SUM) can take place anywhere in a column.

There could be as many as 5 or 6 contiguous value cells or only one.

No other scenario.

See my other post for a method which does work but uses for/each.

I'll wait for Isabelle or Don but I know Don is watching the "horns"
game.

No rush.
Gord



On Thu, 24 Nov 2011 16:32:31 -0800, "Jim Cone"
wrote:
Gord,
Maybe...
ActiveCell.Formula = "=A3+MIN(LEN(A1)<0,LEN(A2)<0)*(A1+A2)"

Also, Isabelle ought to be along any minute with something better. <g
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware




"Gord Dibben"

wrote in message
...
I am stuck with VBA syntax.
A1 has value of 123

A2 is blank

A3 also has value of 123

In A4 need a SUM range of A3 only

If A1:A3 all have values then need SUM range of A1:A3

This is what I have now but does not do the variable trick.

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1,0).End(xlUp))

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"

Gord

Variable SUM range
 

I have read your posts a few times and am not 100% sure what you are after.
Are you looking for the address of the last contiguous range of cells in
Column A above the active cell (where the active cell is in Column A)? If so
and if your entries are constants, then I think this will do what you
want...

Dim R As Range
Set R = Range("A1:A" & ActiveCell.Offset(-1).Row).SpecialCells(xlConstants)
ActiveCell.Formula = "=Sum(" & R.Areas(R.Areas.Count).Address & ")"

Rick Rothstein (MVP - Excel)

Variable SUM range
 

if you are looking for the last empty cell in the range A1: A7
and then sum from this line at the end of rng3

1
-
-
4
-
6
7

Set rng3 = Range("A1:A" & ActiveCell.Row - 1)
rw = Evaluate("MAX(IF(" & rng3.Address & "="""",ROW(" & rng3.Address & ")))")
rng = Cells(rw, rng3.Column).Address & ":" & rng3(rng3.Count).Address
ActiveCell.Formula = "=Sum(" & rng & ")"


--
isabelle

Variable SUM range
 

This would be for the general case where the active cell could be in any
column...

Dim R As Range
Set R = Range(ActiveCell.EntireColumn.Cells(1), _
ActiveCell.Offset(-1)).SpecialCells(xlConstants)
ActiveCell.Formula = "=Sum(" & R.Areas(R.Areas.Count).Address & ")"

Rick Rothstein (MVP - Excel)

Variable SUM range
 

or

Set rng3 = Range("A1:A7")
rw = Evaluate("MAX(IF(" & rng3.Address & "="""",ROW(" & rng3.Address & "))," & rng3.Row & ")")
rng = Cells(rw, rng3.Column).Address & ":" & rng3(rng3.Count).Address
ActiveCell.Formula = "=Sum(" & rng & ")"


--
isabelle

Variable SUM range
 

On Thu, 24 Nov 2011 15:20:53 -0800, Gord Dibben wrote:

I am stuck with VBA syntax.


A1 has value of 123

A2 is blank

A3 also has value of 123

In A4 need a SUM range of A3 only

If A1:A3 all have values then need SUM range of A1:A3

This is what I have now but does not do the variable trick.

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1,0).End(xlUp))

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"


Gord

Reading through the thread it seems that what you want is

With regard to ActiveCell
Insert a formula that SUMS everything from the cell above back up to the first blank cell in the column (or the top of the column).

That being the case, try:

=====================
Option Explicit
Sub SumRangeAbove()
Dim rg As Range
Set rg = ActiveCell.Offset(rowoffset:=-1)
Set rg = Range(rg, rg.End(xlUp))
ActiveCell.Formula = "=SUM(" & rg.Address & ")"
End Sub
=========================

What do you want to do if the cell above the active cell is blank? The method above will return a SUM function that references the first non-blank cell above. If you want to return nothing, test the cell above ActiveCell to ensure it has content before entering the formula.

Variable SUM range
 

On Fri, 25 Nov 2011 08:15:13 -0500, Ron Rosenfeld wrote:

On Thu, 24 Nov 2011 15:20:53 -0800, Gord Dibben wrote:

I am stuck with VBA syntax.


A1 has value of 123

A2 is blank

A3 also has value of 123

In A4 need a SUM range of A3 only

If A1:A3 all have values then need SUM range of A1:A3

This is what I have now but does not do the variable trick.

Set rng3 = Range(ActiveCell.Offset(-1, 0), _
ActiveCell.Offset(-1,0).End(xlUp))

ActiveCell.Formula = "=Sum(" & rng3.Address & ")"


Gord

Reading through the thread it seems that what you want is

With regard to ActiveCell
Insert a formula that SUMS everything from the cell above back up to the first blank cell in the column (or the top of the column).

That being the case, try:

=====================
Option Explicit
Sub SumRangeAbove()
Dim rg As Range
Set rg = ActiveCell.Offset(rowoffset:=-1)
Set rg = Range(rg, rg.End(xlUp))
ActiveCell.Formula = "=SUM(" & rg.Address & ")"
End Sub
=========================


EDIT:

What do you want to do if the cell above the active cell is blank? The method above will return a SUM function that references UP TO first non-blank cell above.

I note that this functionality, including summing up to the first non-blank if the cells above are blank, mimics the SUM button function on the worksheet.

Variable SUM range
 

The column A values were example only.

I wanted to set a range from first non-blank cell above activecell to
first blank cell above that.

EXAMPLE ONLY

A1 123
A2 123
A3 Blank
A4 123
A5 is activecell where total will be.

Range would be A4 only..............123

Fill in A3 and range would be A1:A4.................369

Jim's code has done the job.


Thanks to all.

On Thu, 24 Nov 2011 23:59:00 -0500, "Rick Rothstein"
wrote:

I have read your posts a few times and am not 100% sure what you are after.
Are you looking for the address of the last contiguous range of cells in
Column A above the active cell (where the active cell is in Column A)? If so
and if your entries are constants, then I think this will do what you
want...

Dim R As Range
Set R = Range("A1:A" & ActiveCell.Offset(-1).Row).SpecialCells(xlConstants)
ActiveCell.Formula = "=Sum(" & R.Areas(R.Areas.Count).Address & ")"

Rick Rothstein (MVP - Excel)

Variable SUM range
 

I wanted to set a range from first non-blank cell above
activecell to first blank cell above that.

Jim's code has done the job.

If I am not mistaken, I believe the code I posted (which is slightly more
compact) does that also.

Rick Rothstein (MVP - Excel)

Variable SUM range
 

Yes Rick this code you posted does the job also.

This would be for the general case where the active cell could be in
any column...

Dim R As Range
Set R = Range(ActiveCell.EntireColumn.Cells(1), _
ActiveCell.Offset(-1)).SpecialCells(xlConstants)
ActiveCell.Formula = "=Sum(" & R.Areas(R.Areas.Count).Address & ")"



Thanks..............................Gord

On Fri, 25 Nov 2011 12:17:39 -0500, "Rick Rothstein"
wrote:

I wanted to set a range from first non-blank cell above
activecell to first blank cell above that.

Jim's code has done the job.

If I am not mistaken, I believe the code I posted (which is slightly more
compact) does that also.

Rick Rothstein (MVP - Excel)