Sum Up Dynamic Range
 

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


---
Message posted from http://www.ExcelForum.com/

Sum Up Dynamic Range
 

i,
Try this,

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


Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sum Up Dynamic Range
 

Shah,

It can't work.

Any other suggestions?

Norika


---
Message posted from http://www.ExcelForum.com/

Sum Up Dynamic Range
 

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
norika wrote in message ...
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


---
Message posted from http://www.ExcelForum.com/

Sum Up Dynamic Range
 

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)

"norika " wrote in message ...
Shah,

It can't work.

Any other suggestions?

Norika


---
Message posted from http://www.ExcelForum.com/

Sum Up Dynamic Range
 

Medemper,

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

Any other suggestions?

Norika


---
Message posted from http://www.ExcelForum.com/

Sum Up Dynamic Range
 

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).

"norika " wrote in message
...
Medemper,

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

Any other suggestions?

Norika


---
Message posted from http://www.ExcelForum.com/

Sum Up Dynamic Range
 

Actually, I want the results showing in the top of dynamic range.

For example , in result showed in Cell A1
if the data a
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


---
Message posted from http://www.ExcelForum.com/