| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.FormulaArray code - Get error
Hi,
I have a formula I am running in a workbook to calculate the averages of a column based on values in another column. I ran this same code on another workbook last week without any problems, but this time I am getting the Run Rime Error - 1004 - Unable to Set the FormulaArray Property of the Range Class ActiveCell.FormulaArray = "=IF(ISERROR(AVERAGE(IF ([NUMEARNEST_30_60d_" & EDate & ".xls]30d_win!$B$4:$B" & _ lRow & "=C10,[NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & "))),0.000,AVERAGE(IF([NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$B$4:$B$" & lRow & "=C10, [NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & ")))" The formula works fine if I manually enter it and create the array, only in VBA does it give the error. I'm using XCL '02. How can I get around this error. I have lots of these calculations to do for many dates. Thx a bunch! Kohai |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveCell.FormulaArray code - Get error
I believe a formulaArray formula is restricted to a length of 255 characters
and yours appears to be longer than that. -- Regards, Tom Ogilvy "Kohai" wrote in message ... Hi, I have a formula I am running in a workbook to calculate the averages of a column based on values in another column. I ran this same code on another workbook last week without any problems, but this time I am getting the Run Rime Error - 1004 - Unable to Set the FormulaArray Property of the Range Class ActiveCell.FormulaArray = "=IF(ISERROR(AVERAGE(IF ([NUMEARNEST_30_60d_" & EDate & ".xls]30d_win!$B$4:$B" & _ lRow & "=C10,[NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & "))),0.000,AVERAGE(IF([NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$B$4:$B$" & lRow & "=C10, [NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & ")))" The formula works fine if I manually enter it and create the array, only in VBA does it give the error. I'm using XCL '02. How can I get around this error. I have lots of these calculations to do for many dates. Thx a bunch! Kohai |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveCell.FormulaArray code - Get error
Tom,
Thanks for your input, but I checked a formula that is working (typed in, not done by VBA) and it's 264 characters, and it does work properly. I had this code work last week in a similar wbk. Can't understand why it won't work now. -----Original Message----- I believe a formulaArray formula is restricted to a length of 255 characters and yours appears to be longer than that. -- Regards, Tom Ogilvy "Kohai" wrote in message ... Hi, I have a formula I am running in a workbook to calculate the averages of a column based on values in another column. I ran this same code on another workbook last week without any problems, but this time I am getting the Run Rime Error - 1004 - Unable to Set the FormulaArray Property of the Range Class ActiveCell.FormulaArray = "=IF(ISERROR(AVERAGE(IF ([NUMEARNEST_30_60d_" & EDate & ".xls]30d_win!$B$4:$B" & _ lRow & "=C10,[NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & "))),0.000,AVERAGE(IF([NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$B$4:$B$" & lRow & "=C10, [NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & ")))" The formula works fine if I manually enter it and create the array, only in VBA does it give the error. I'm using XCL '02. How can I get around this error. I have lots of these calculations to do for many dates. Thx a bunch! Kohai . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveCell.FormulaArray code - Get error
I didn't say an array formula, I said the formulaarray property. So yes,
you can type in an array formula that is greater than 255 characters, but that is not what you asked about and isn't what you are having a problem with. the formulaarray property will not accept an argument greater than 255 characters. Additionally, I believe the length restiction is based on its length when expressed in the R1C1 format (although you don't have to supply it in that format). -- Regards, Tom Ogilvy "Kohai" wrote in message ... Tom, Thanks for your input, but I checked a formula that is working (typed in, not done by VBA) and it's 264 characters, and it does work properly. I had this code work last week in a similar wbk. Can't understand why it won't work now. -----Original Message----- I believe a formulaArray formula is restricted to a length of 255 characters and yours appears to be longer than that. -- Regards, Tom Ogilvy "Kohai" wrote in message ... Hi, I have a formula I am running in a workbook to calculate the averages of a column based on values in another column. I ran this same code on another workbook last week without any problems, but this time I am getting the Run Rime Error - 1004 - Unable to Set the FormulaArray Property of the Range Class ActiveCell.FormulaArray = "=IF(ISERROR(AVERAGE(IF ([NUMEARNEST_30_60d_" & EDate & ".xls]30d_win!$B$4:$B" & _ lRow & "=C10,[NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & "))),0.000,AVERAGE(IF([NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$B$4:$B$" & lRow & "=C10, [NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & ")))" The formula works fine if I manually enter it and create the array, only in VBA does it give the error. I'm using XCL '02. How can I get around this error. I have lots of these calculations to do for many dates. Thx a bunch! Kohai . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
ActiveCell.FormulaArray code - Get error
Sorry, I didn't follow your first reply. I'll have to
check what worked last time and see if it was < 255 char. (Prob was!) Thanks! -----Original Message----- I didn't say an array formula, I said the formulaarray property. So yes, you can type in an array formula that is greater than 255 characters, but that is not what you asked about and isn't what you are having a problem with. the formulaarray property will not accept an argument greater than 255 characters. Additionally, I believe the length restiction is based on its length when expressed in the R1C1 format (although you don't have to supply it in that format). -- Regards, Tom Ogilvy "Kohai" wrote in message ... Tom, Thanks for your input, but I checked a formula that is working (typed in, not done by VBA) and it's 264 characters, and it does work properly. I had this code work last week in a similar wbk. Can't understand why it won't work now. -----Original Message----- I believe a formulaArray formula is restricted to a length of 255 characters and yours appears to be longer than that. -- Regards, Tom Ogilvy "Kohai" wrote in message ... Hi, I have a formula I am running in a workbook to calculate the averages of a column based on values in another column. I ran this same code on another workbook last week without any problems, but this time I am getting the Run Rime Error - 1004 - Unable to Set the FormulaArray Property of the Range Class ActiveCell.FormulaArray = "=IF(ISERROR(AVERAGE(IF ([NUMEARNEST_30_60d_" & EDate & ".xls]30d_win! $B$4:$B" & _ lRow & "=C10,[NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & "))),0.000,AVERAGE(IF([NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$B$4:$B$" & lRow & "=C10, [NUMEARNEST_30_60d_" & _ EDate & ".xls]30d_win!$R$4:$R$" & lRow & ")))" The formula works fine if I manually enter it and create the array, only in VBA does it give the error. I'm using XCL '02. How can I get around this error. I have lots of these calculations to do for many dates. Thx a bunch! Kohai . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
| FormulaArray..... HELP !!! | Excel Programming | |||
| How to avoid error 2015 when using ActiveCell.Offsett in own function | Excel Programming | |||
| code to change activecell | Excel Programming | |||
| unable to set formulaarray of range class ERROR when using Conditional Sum | Excel Programming | |||
Linear Mode
