using the address properties from range objects in a sum formula

[Guest]

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

 

[RagDyer]

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?

 

[Guest]

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.