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/

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!

Shah,

It can't work.

Any other suggestions?

Norika


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

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/

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/

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/

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/