how to place a variable cell into a range?
 

Have a sheet with approx 3500 names and varying data . column A is
dates, J thru Z is dollars. I can easily sort and find the last dated
entry - Range("A5000").Select
Selection.End(xlUp).Select

but how can I get column Z in"that" row to be part of a range to sum?
something like =sum( Z9 : thenewZcell ).

I'm trying to find the remaining amount between the entire Z9 to
Z3500, and what has been collected to date (Z9 to Z??) and show that
difference in Z3.

Thnx

how to place a variable cell into a range?
 

Ozoned

Range("z65536").End(xlUp).Select
MsgBox Evaluate("=sum(z9:" & ActiveCell.Address & ")")

Tony
-----Original Message-----
Have a sheet with approx 3500 names and varying data .
column A is
dates, J thru Z is dollars. I can easily sort and find
the last dated
entry - Range("A5000").Select
Selection.End(xlUp).Select

but how can I get column Z in"that" row to be part of a
range to sum?
something like =sum( Z9 : thenewZcell ).

I'm trying to find the remaining amount between the
entire Z9 to
Z3500, and what has been collected to date (Z9 to Z??)
and show that
difference in Z3.

Thnx
.

how to place a variable cell into a range?
 

cRows = Cells(Rows.Count,"Z").End(xlUp).Row
Cells(cRows+1,"Z").FormulaR1C1 = "=SUM(R9C26:R" & cRows & "C26)"

this puts the formula in column Z, last row +1

--

HTH

Bob Phillips

"ozoned" <ozoned@anonymous wrote in message
...
Have a sheet with approx 3500 names and varying data . column A is
dates, J thru Z is dollars. I can easily sort and find the last dated
entry - Range("A5000").Select
Selection.End(xlUp).Select

but how can I get column Z in"that" row to be part of a range to sum?
something like =sum( Z9 : thenewZcell ).

I'm trying to find the remaining amount between the entire Z9 to
Z3500, and what has been collected to date (Z9 to Z??) and show that
difference in Z3.

Thnx