Variable SUM range

[Gord Dibben]

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

 

[Gord Dibben]

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

 

[Jim Cone]

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"
<[email protected]>
wrote in message

 

[Gord Dibben]

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

 

[isabelle]

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]

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

 

[Gord Dibben]

Thanks Isabelle.

I'll work on these.


Gord

 

[isabelle]

or +more général

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


@+

 

[Jim Cone]

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" <[email protected]>
wrote in message

 

[Gord Dibben]

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

 

[Rick Rothstein]

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)

 

[isabelle]

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 & ")"

 

[Rick Rothstein]

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)

 

[isabelle]

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 & ")"

 

[Gord Dibben]

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.

 

[Rick Rothstein]

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)

 

[Gord Dibben]

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