how to place a variable cell into a range?

O

ozoned

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
 
A

acw

Ozoned

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

Tony
 
B

Bob Phillips

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to Format Multiple Sheets based on "Master" Format 0
Range to Variable for Sort 2
Referring to ranges as Cell, Range, and variables 6
Delete records from result-cell after a find-function call 7
Repeat the macro using different ranges 1
Use count data in formula 1
Compating all rows in two sheets 2
Delete variable range when cell is emtpy 8

Top