Sum Up Dynamic Range

  • Thread starter Thread starter norika
  • Start date Start date
N

norika

Hi,

I write macro to sum up dynamic range.

Dim rng As Range
Dim sumup As Double

Range("A1").Select

Set rng = Range(ActiveCell.Offset(1, 0), ActiveCell(1, 0).End(xlDown))
sumup = Application.Sum(rng)

But it cannot work.

The run-time error '1004' : application-defined or object-defined
error

How can i correct the error?

Also, how to write macro if the active cell was in the bottom of range?
Is it Set rng = Range(ActiveCell.Offset(-1, 0), ActiveCell(-1,
0).End(xlUp))

Thanks in advance.

Norika
 
Hi,

In the definition of rng you have ActiveCell(1, 0). Do you mean
ActiveCell.offset(1, 0)?

"Also, how to write macro if the active cell was in the bottom of
range?
Is it Set rng = Range(ActiveCell.Offset(-1, 0), ActiveCell(-1,
0).End(xlUp))"

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

should be OK and will stop at the first empty cell looking up.
regards
Paul
 
Dim rng As Range
Dim sumup As Double

Range("A1").Select

Set rng = Range(ActiveCell.Offset(1, 0).Address, ActiveCell.Offset(1, 0).End(xlDown).Address)
sumup = Application.Sum(rng)
 
Medemper,

It is only going to Cell A1, but it does not sum up the range.

Any other suggestions?

Norika
 
I was assuming that this code was part of a bigger macro. When I "step
into" this part of the macro, my sumup variable has a sum of the range until
the macro ends.

Where are you wanting the sum to show up? The other thing is that this
macro is not designed to leave cells selected when its done, so only Cell A1
will be selected when its done.

We can set a cell to the value of sumup if that's what you need it to do.

Also note that it will not add the value of cell A1 in the sum since we are
doing an activecell.offset(1,0).
 
Actually, I want the results showing in the top of dynamic range.

For example , in result showed in Cell A1
if the data are:
A2 2
A3 3
A4 4

The result in Cell A1 = 9

The macro was not too big because i am new learner of macro.

Norika
 
Back
Top