I couldn't figure out what do you want to do (eg. what is "IS" in Sumif
formula, where LAST without a number is defined), but
ActiveCell.formula = "=SUMIF(D7:LAST,""IS"",D8:LASTi)"
should be written in this way:
ActiveCell.formula = "=SUMIF(D7:LAST,""IS"",D8:LAST" & i & ")"
Regards,
Stefi
€˛lesley1000 via OfficeKB.com€¯ ezt Ć*rta:
I have named in excel 70 cells - I have named them LAST1, LAST2,..., LAST70.
They are in the same column, one underneath the other.
I want to use the following bit of code:
ActiveCell.formula = "=SUMIF(D7:LAST,""IS"",D8:LAST1)"
on the right hand side of each cell (as where these "LAST" cells will change
on diffferent worksheets).
Instead of me typing this formula out 70 times, is there a way of defining
the names? I've tried the following way:
Dim i
For i = 1 To 70
ActiveCell.formula = "=SUMIF(D7:LAST,""IS"",D8:LASTi)"
ActiveCell.Offset(1, 0).Select
for next i
This will allow me to enter the formula in each cell right hand side of the
Named ones - but VBA doesn't like how I've definied this variable "i" in the
formula. I've tried &i, putting it in "", putting spaces, but I either get
errors in VBA or NAME? in the cell
Any help?
--
Message posted via http://www.officekb.com