How to create Dynamic interrelated ranges in excel

[svijay]

Dear All,

Im facing a problem. Could any one have any suggestions to me to solv
my problem.

I have got a work sheet, in that I defined 2 ranges with the Range_
and Range_2.

Range_1 initially I defined as "A1:B1" (i.e one row and 2 columns)

In Range_1 the first column (i.e Cell A) contains description
and second column (i.e cell B) contains Amount.

Range_2 initially referring "B2"

I open the recordset and for each record I dynamically access "Range_1
and I increase one row
and assign values to the respecitve column and also increasing the Ro
in Range_2 with respective
of Range_1.

i.e the row that is referring Range_2 is always the after the last ro
of Range_1.

So finally my rows in Range_1 contains as many rows existing in m
database table.

Now the problem is I need to sum the amount in Range_1 after addin
each and every record
and I need to assign the sum to the cell that is pointing to Range_2.

One way that I can do is dynamically assigning the formula to Range_
cell after knowing
in which row Range_2 ends.

But Im doing using Excel Template.

So I need to define all in template it self like Range_1 and Range_
definitions.
Now I would like to know how if I define the formula for Range_2 s
that it dynamically
takes the Range_1 starting and ending row and calulate the sum and als
I would like to know
is there any way to define a Range relative to another range.

i.e if the first range dimensions changed then the second rang
dimensions shd change accordingly.

Thanks once again.
Vija

 

[Bernie Deitrick]

Vijay,

You don't really need Range_2.

If you are using something like
Dim Range_1 As Range
.....
then
Range_1(Range_1.Cells.Count + 2)
will always be the cell one row down from your block of cells.

To put a sum in that cell, you could use something like this, which will sum
the second column of your Range_1:

Range_1(Range_1.Cells.Count + 2).Formula = _
"=SUM(" & Range_1.Columns(2).Address & ")"

If you are using a named range, then simply change all instances of Range_1
to Range("Range_1") in the code.

HTH,
Bernie
MS Excel MVP

 

[svijay]

Hi Bernie

Thx a lot giving solution. I have got the idea conceptually and let m
try this by implementing.

Once again thx a lot for your help.

cheers
Vija