ActiveCell.FormulaArray code - Get error

[Kohai]

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

 

[Tom Ogilvy]

I believe a formulaArray formula is restricted to a length of 255 characters
and yours appears to be longer than that.

 

[Kohai]

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

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

.

 

[Tom Ogilvy]

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

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

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

.

 

[Kohai]

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

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

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


.

.