Unable to set FormulaArray property VBA

[MJP]

I'm trying to populate a number of cells with an array formula and can't
seem to get it to work. Every time I try to run the code I get the 'Unable
to set FormulaArray property of the range class' message.

A previous posting suggested this was due to there being a limit of 256
characters to a VBA formulaarray. The formula I am using includes a long
file path and name as it references another workbook. This made the formula
over 256 characters, so I have tried to replace the file path with a
variable that it populated with the file path stored in a specific cell
(e.g. fold = a1 with a1 containing the full file ref.)

Despite trying a number of combinations I can't get it to work. My latest
stab at the formula is as follows :

s_Address = Cells(r, c).AddressLocal(True, False, xlA1)

s_ColumnName = Left(s_Address, InStr(1, _
s_Address, "$") - 1)

wtype_ref = s_ColumnName & wtype_row & "="
date_ref = "A" & r & "="

fold = Cells(2, 10).Value

Range(Cells(r, c), Cells(r, c)).FormulaArray = "=SUM(IF($C$3=" & fold &
"B$3:$B$311, IF(" & wtype_ref & fold & "$F$3:$F$311, IF(" & date_ref & fold
& "$E$3:$E$311," & fold & "$D$3:$D$311))))"

the value in cell(2,10) is the file path & file name & sheet name -
'G:\Data\M\[SCL.xls]D'!

Can anyone see where I am going wrong? The reason I am using wtype_ref in
the formula is that my code has to populate this formula into several
consecutive cells in a row and so the reference changes for each cell.

If I use the full file path in the above formula and enter it directly into
a cell(rather than by VBA) it works fine, so I believe my array is OK, but
suspect it's something to do with the variable I'm using to represent the
file reference?

Please help as I've spent what should have been a productive day in the
office working on this damn formula!!!

Thanks in advance,

Mitch

 

[Tim Zych]

I can't get your formula to work. When I debug.print it, I get:

=SUM(IF($C$3='D:\Data\[Test.xls]D'!B$3:$B$311,
IF(A='D:\Data\[Test.xls]D'!$F$3:$F$311,
IF(A5='D:\Data\[Test.xls]D'!$E$3:$E$311,'D:\Data\[Test.xls]D'!$D$3:$D$311)))
)

which is incorrect.

I'm not sure if I know what you are trying to do, so maybe my attempts at
setting r and c to some arbitrary row and column reference are not what you
had in mind.

So to start, what is the formula you successfully enter?

 

[MJP]

Tim,

Yes sorry, I should have included the line of code from the sub that sets
wtype_row to equal the current row number.

I believe the problem is that the actual working formula is:

=SUM(IF($C$3='G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$B$3:$B$311,
IF(AA11='G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$F$3:$F$311,
IF(A14='G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$E$3:$E$311,
'G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$D$3:$D$311))))

clearly this is way over 256 characters and so I have tried to get around
this by instead of using the file path & name in the formula using a
variable called fold, which points to a cell containing 'G:\Data\Membership
services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!

(the wtpe_ref & date_ref parts are purely to change the cell refs as I am
populating a number of cells with the formula).

In a previous posting you said to try and use a named range to get around
the 256 char problem, which is what I am trying to do, but I don't think
what I've come up with is really what you meant? I'm assuming the reason it
still isn't working is that VBA is looking at the length of the fold
variable as part of the formula length?

In simplest terms, can I build an array formula in VBA that replaces the
full file path & name with a range name instead to get around the 256 char
problem? If so, how?

Thanks in advance,

Mitch

I can't get your formula to work. When I debug.print it, I get:

=SUM(IF($C$3='D:\Data\[Test.xls]D'!B$3:$B$311,
IF(A='D:\Data\[Test.xls]D'!$F$3:$F$311,
IF(A5='D:\Data\[Test.xls]D'!$E$3:$E$311,'D:\Data\[Test.xls]D'!$D$3:$D$311)))
)

which is incorrect.

I'm not sure if I know what you are trying to do, so maybe my attempts at
setting r and c to some arbitrary row and column reference are not what you
had in mind.

So to start, what is the formula you successfully enter?

I'm trying to populate a number of cells with an array formula and can't
seem to get it to work. Every time I try to run the code I get the 'Unable
to set FormulaArray property of the range class' message.

A previous posting suggested this was due to there being a limit of 256
characters to a VBA formulaarray. The formula I am using includes a long
file path and name as it references another workbook. This made the formula
over 256 characters, so I have tried to replace the file path with a
variable that it populated with the file path stored in a specific cell
(e.g. fold = a1 with a1 containing the full file ref.)

Despite trying a number of combinations I can't get it to work. My latest
stab at the formula is as follows :

s_Address = Cells(r, c).AddressLocal(True, False, xlA1)

s_ColumnName = Left(s_Address, InStr(1, _
s_Address, "$") - 1)

wtype_ref = s_ColumnName & wtype_row & "="
date_ref = "A" & r & "="

fold = Cells(2, 10).Value

Range(Cells(r, c), Cells(r, c)).FormulaArray = "=SUM(IF($C$3=" & fold &
"B$3:$B$311, IF(" & wtype_ref & fold & "$F$3:$F$311, IF(" & date_ref & fold
& "$E$3:$E$311," & fold & "$D$3:$D$311))))"

the value in cell(2,10) is the file path & file name & sheet name -
'G:\Data\M\[SCL.xls]D'!

Can anyone see where I am going wrong? The reason I am using wtype_ref in
the formula is that my code has to populate this formula into several
consecutive cells in a row and so the reference changes for each cell.

If I use the full file path in the above formula and enter it directly into
a cell(rather than by VBA) it works fine, so I believe my array is OK, but
suspect it's something to do with the variable I'm using to represent the
file reference?

Please help as I've spent what should have been a productive day in the
office working on this damn formula!!!

Thanks in advance,

Mitch

 

[Tim Zych]

Hi. I meant to use a range names instead of cell references, if that's
possible. You are right...by the time you concatenate the path and filename
you've got well over 255 characters.

Anyway, I don't think you need an array formula. Sumproduct seems to work:

ActiveCell.Formula = "=SUMPRODUCT(($C$3='G:\Data\Membership
services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$B$3:$B$311)*(AA11='G:\Data\Mem
bership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$F$3:$F$311)*(A14='G:\Data\Memb
ership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$E$3:$E$311)*('G:\Data\Membersh
ip services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$D$3:$D$311))"

You may need to tweak the path a bit.


Hth,
Tim Zych

Tim,

Yes sorry, I should have included the line of code from the sub that sets
wtype_row to equal the current row number.

I believe the problem is that the actual working formula is:

=SUM(IF($C$3='G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$B$3:$B$311,
IF(AA11='G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$F$3:$F$311,
IF(A14='G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$E$3:$E$311,
'G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$D$3:$D$311))))

clearly this is way over 256 characters and so I have tried to get around
this by instead of using the file path & name in the formula using a
variable called fold, which points to a cell containing 'G:\Data\Membership
services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!

(the wtpe_ref & date_ref parts are purely to change the cell refs as I am
populating a number of cells with the formula).

In a previous posting you said to try and use a named range to get around
the 256 char problem, which is what I am trying to do, but I don't think
what I've come up with is really what you meant? I'm assuming the reason it
still isn't working is that VBA is looking at the length of the fold
variable as part of the formula length?

In simplest terms, can I build an array formula in VBA that replaces the
full file path & name with a range name instead to get around the 256 char
problem? If so, how?

Thanks in advance,

Mitch

I can't get your formula to work. When I debug.print it, I get:

=SUM(IF($C$3='D:\Data\[Test.xls]D'!B$3:$B$311,
IF(A='D:\Data\[Test.xls]D'!$F$3:$F$311,

IF(A5='D:\Data\[Test.xls]D'!$E$3:$E$311,'D:\Data\[Test.xls]D'!$D$3:$D$311)))

)

which is incorrect.

I'm not sure if I know what you are trying to do, so maybe my attempts at
setting r and c to some arbitrary row and column reference are not what you
had in mind.

So to start, what is the formula you successfully enter?

I'm trying to populate a number of cells with an array formula and can't
seem to get it to work. Every time I try to run the code I get the 'Unable
to set FormulaArray property of the range class' message.

A previous posting suggested this was due to there being a limit of 256
characters to a VBA formulaarray. The formula I am using includes a long
file path and name as it references another workbook. This made the formula
over 256 characters, so I have tried to replace the file path with a
variable that it populated with the file path stored in a specific cell
(e.g. fold = a1 with a1 containing the full file ref.)

Despite trying a number of combinations I can't get it to work. My latest
stab at the formula is as follows :

s_Address = Cells(r, c).AddressLocal(True, False, xlA1)

s_ColumnName = Left(s_Address, InStr(1, _
s_Address, "$") - 1)

wtype_ref = s_ColumnName & wtype_row & "="
date_ref = "A" & r & "="

fold = Cells(2, 10).Value

Range(Cells(r, c), Cells(r, c)).FormulaArray = "=SUM(IF($C$3=" & fold &
"B$3:$B$311, IF(" & wtype_ref & fold & "$F$3:$F$311, IF(" & date_ref & fold
& "$E$3:$E$311," & fold & "$D$3:$D$311))))"

the value in cell(2,10) is the file path & file name & sheet name -
'G:\Data\M\[SCL.xls]D'!

Can anyone see where I am going wrong? The reason I am using

wtype_ref

in

 

[MJP]

Thanks Tim, that does work.

I've also sussed what I was doing wrong with the ranges - I hadn't realised
I could define a range that was in another workbook. Once I'd done this and
used these range names in the formula it worked fine.

Thanks for your help, it's great to have a good samaritan on hand when
you've been struggling with an Excel problem for many hours!

Mitch

Hi. I meant to use a range names instead of cell references, if that's
possible. You are right...by the time you concatenate the path and filename
you've got well over 255 characters.

Anyway, I don't think you need an array formula. Sumproduct seems to work:

ActiveCell.Formula = "=SUMPRODUCT(($C$3='G:\Data\Membership
services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$B$3:$B$311)*(AA11='G:\Data\Mem
bership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$F$3:$F$311)*(A14='G:\Data\Memb
ership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$E$3:$E$311)*('G:\Data\Membersh
ip services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$D$3:$D$311))"

You may need to tweak the path a bit.


Hth,
Tim Zych

Tim,

Yes sorry, I should have included the line of code from the sub that sets
wtype_row to equal the current row number.

I believe the problem is that the actual working formula is:

=SUM(IF($C$3='G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$B$3:$B$311,
IF(AA11='G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$F$3:$F$311,
IF(A14='G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$E$3:$E$311,
'G:\Data\Membership services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!$D$3:$D$311))))

clearly this is way over 256 characters and so I have tried to get around
this by instead of using the file path & name in the formula using a
variable called fold, which points to a cell containing 'G:\Data\Membership
services\Productivity Model\New Model
8_07_03\[SanitisedClosedTasksLog.xls]Sheet1'!

(the wtpe_ref & date_ref parts are purely to change the cell refs as I am
populating a number of cells with the formula).

In a previous posting you said to try and use a named range to get around
the 256 char problem, which is what I am trying to do, but I don't think
what I've come up with is really what you meant? I'm assuming the

reason

it
still isn't working is that VBA is looking at the length of the fold
variable as part of the formula length?

In simplest terms, can I build an array formula in VBA that replaces the
full file path & name with a range name instead to get around the 256 char
problem? If so, how?

Thanks in advance,

Mitch

I can't get your formula to work. When I debug.print it, I get:

=SUM(IF($C$3='D:\Data\[Test.xls]D'!B$3:$B$311,
IF(A='D:\Data\[Test.xls]D'!$F$3:$F$311,

IF(A5='D:\Data\[Test.xls]D'!$E$3:$E$311,'D:\Data\[Test.xls]D'!$D$3:$D$311)))

what
you

fold

&

"B$3:$B$311, IF(" & wtype_ref & fold & "$F$3:$F$311, IF(" & date_ref &
fold
& "$E$3:$E$311," & fold & "$D$3:$D$311))))"

the value in cell(2,10) is the file path & file name & sheet name -
'G:\Data\M\[SCL.xls]D'!

Can anyone see where I am going wrong? The reason I am using

wtype_ref

in
the formula is that my code has to populate this formula into several
consecutive cells in a row and so the reference changes for each cell.

If I use the full file path in the above formula and enter it directly
into
a cell(rather than by VBA) it works fine, so I believe my array is

OK,
but

suspect it's something to do with the variable I'm using to

represent
the

file reference?

Please help as I've spent what should have been a productive day in the
office working on this damn formula!!!

Thanks in advance,

Mitch