Defined names

[lesley1000 via OfficeKB.com]

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?

 

[Stefi]

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:

 

[lesley1000 via OfficeKB.com]

Fantastic, thank you for that - I knew that there was a way!

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.

[quoted text clipped - 19 lines]

Any help?

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„lesley1000 via OfficeKB.com†ezt írta:

Fantastic, thank you for that - I knew that there was a way!

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.

[quoted text clipped - 19 lines]

Any help?