I am trying to create a sum formula that uses two range objects as the input
to the sum formula. For example I have the following ranges dimensioned:

Dim r1 As Range, r2 As Range

Further down in the code have the following lines:

Range("c22").select
Set r1 = Range(ActiveCell.Address)
Range("C40").Select
Set r2 = Range(ActiveCell.Address)

Now I want to do a sum formula using these two ranges. I then go to a blank
cell in code and try to enter this:

= sum(r1.Address:r2.Address)

When i try this it doesn't compile and throws an error that says expected
list seperator or ).

Can someone point me to some documentation or otherwise advise me on how I
should be approaching this problem?

Thank you very much!
JEFF

Don't know the first thing about code, but if the "Address" that you're
using in your code is anything like the Address() that I would use as a
function, then your problem could possibly be that Address() returns a
*TEXT* value!

For example:
A1 = 5
B1 = 6
F1 = 2
F5 = 10

=Address(A1,B1)
returns $F$5

SO,
=F1*Address(A1,B1)
Appears that it should return 20,
BUT ... it returns a #Value! error.

To convert the TEXT return of Address() to an XL usable cell reference,
you'll need a formula something like this:

=F1*INDIRECT(ADDRESS(A1,B1))

Is this possibly what you might be up against in your code?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JEFFWI" wrote in message
...
I am trying to create a sum formula that uses two range objects as the
input
to the sum formula. For example I have the following ranges dimensioned:

Dim r1 As Range, r2 As Range

Further down in the code have the following lines:

Range("c22").select
Set r1 = Range(ActiveCell.Address)
Range("C40").Select
Set r2 = Range(ActiveCell.Address)

Now I want to do a sum formula using these two ranges. I then go to a
blank
cell in code and try to enter this:

= sum(r1.Address:r2.Address)

When i try this it doesn't compile and throws an error that says expected
list seperator or ).

Can someone point me to some documentation or otherwise advise me on how
I
should be approaching this problem?

Thank you very much!
JEFF

Thanks Rag! I solved my problem by naming ranges for the top and bottom cell
that I want to sum then using =sum(top:bottom) in the code.

"RagDyer" wrote:

Don't know the first thing about code, but if the "Address" that you're
using in your code is anything like the Address() that I would use as a
function, then your problem could possibly be that Address() returns a
*TEXT* value!

For example:
A1 = 5
B1 = 6
F1 = 2
F5 = 10

=Address(A1,B1)
returns $F$5

SO,
=F1*Address(A1,B1)
Appears that it should return 20,
BUT ... it returns a #Value! error.

To convert the TEXT return of Address() to an XL usable cell reference,
you'll need a formula something like this:

=F1*INDIRECT(ADDRESS(A1,B1))

Is this possibly what you might be up against in your code?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"JEFFWI" wrote in message
...
I am trying to create a sum formula that uses two range objects as the
input
to the sum formula. For example I have the following ranges dimensioned:

Dim r1 As Range, r2 As Range

Further down in the code have the following lines:

Range("c22").select
Set r1 = Range(ActiveCell.Address)
Range("C40").Select
Set r2 = Range(ActiveCell.Address)

Now I want to do a sum formula using these two ranges. I then go to a
blank
cell in code and try to enter this:

= sum(r1.Address:r2.Address)

When i try this it doesn't compile and throws an error that says expected
list seperator or ).

Can someone point me to some documentation or otherwise advise me on how
I
should be approaching this problem?

Thank you very much!
JEFF