Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined names
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined names
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined names
Fantastic, thank you for that - I knew that there was a way!
Stefi wrote: 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? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200712/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined names
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! Stefi wrote: 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? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200712/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to keep names defined after a copy? | Excel Discussion (Misc queries) | |||
NAMES DEFINED | Excel Discussion (Misc queries) | |||
List of defined names | Excel Worksheet Functions | |||
Defined names | Excel Discussion (Misc queries) | |||
Using non defined names from another sheet | Excel Worksheet Functions |