VBA and FormulaArray Runtime Err <is not more than 255 Char>

  • Thread starter Thread starter Kohai
  • Start date Start date
K

Kohai

Hi,

I am running a looping process that pastes a formula
array into a wbk from data in another wbk. The
underlying books are all based on the same template, just
data for different months. After the data is created, I
save the book and save it also as X.xls to shorten the
name (to keep under 255). This formula array worked over
1000 times. All of a sudden I get to a month where I am
getting the Runtime error -1004. The length of the
formula is 196 char

VBA
ActiveCell.Formula = "=((SUM(((" & dataBk2 & "$C11:$C" &
lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" &
dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C" &
_
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & ")))^
(1/5)-1)*100"

ACTUAL RESULT STRING
=((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))*(([x.xls]S1!$AN11:$AN1509)))/SUM
((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!$H11:$H1509=500))
*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100

If I take the above formula and paste it into the formula
bar and hit ctrl-shft-entr it does work and returns the
correct value.

So, if it's not the 255 char limit problem, I can't seem
to figure out why it works for so many months prior, and
this file is identical in layout that it it would
suddenly not work. There are no errors in the underlying
value. I'm going bonkers b/c all the files are the
same. WHAT CAN I MISSING????? HELP!!!!

TIA,
Kohai

ps - sorry for the lengthy question.
 
What happens if you use?

Dim myForm As String

myForm = "=((SUM(((" & dataBk2 & "$C11:$C" & lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" & dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C" & _
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & ")))^ (1/5)-1)*100"

ActiveCell.Formula = myForm

Though don't you really want

ActiveCell.FormulaArray ??

HTH,
Bernie
MS Excel MVP
 
Bernie,

Thx for responding. It needs to be an array since it is
using multiple criteria (what's in C and H) to sum the
two columns AN & AO. Eventually, I'm going to need to
add a 3rd criteria, so I think I'm going to have to
actually do the math in VBA and have it return the result
rather than use the FormulaArray. That 255 char limit is
a real stinker. But the current formula is <255 and it
works on identical files. Can't figure out why it
fails.
-----Original Message-----
What happens if you use?

Dim myForm As String

myForm = "=((SUM(((" & dataBk2 & "$C11:$C" & lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" & dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C" & _
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & "))) ^ (1/5)-1)*100"

ActiveCell.Formula = myForm

Though don't you really want

ActiveCell.FormulaArray ??

HTH,
Bernie
MS Excel MVP

Hi,

I am running a looping process that pastes a formula
array into a wbk from data in another wbk. The
underlying books are all based on the same template, just
data for different months. After the data is created, I
save the book and save it also as X.xls to shorten the
name (to keep under 255). This formula array worked over
1000 times. All of a sudden I get to a month where I am
getting the Runtime error -1004. The length of the
formula is 196 char

VBA
ActiveCell.Formula = "=((SUM(((" & dataBk2 & "$C11:$C" &
lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" &
dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C" &
_
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & "))) ^
(1/5)-1)*100"

ACTUAL RESULT STRING
=((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))*(([x.xls]S1!$AN11:$AN1509)))/SUM
((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1! $H11:$H1509=500))
*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100

If I take the above formula and paste it into the formula
bar and hit ctrl-shft-entr it does work and returns the
correct value.

So, if it's not the 255 char limit problem, I can't seem
to figure out why it works for so many months prior, and
this file is identical in layout that it it would
suddenly not work. There are no errors in the underlying
value. I'm going bonkers b/c all the files are the
same. WHAT CAN I MISSING????? HELP!!!!

TIA,
Kohai

ps - sorry for the lengthy question.
Click to expand...


.
Click to expand...
 
Excel actually converts to R1C1 format before doing the insert, and R1C1
tends to be longer.
I suspect that you actually are hitting the 255 character limit in R1C1 mode

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Bernie,

Thx for responding. It needs to be an array since it is
using multiple criteria (what's in C and H) to sum the
two columns AN & AO. Eventually, I'm going to need to
add a 3rd criteria, so I think I'm going to have to
actually do the math in VBA and have it return the result
rather than use the FormulaArray. That 255 char limit is
a real stinker. But the current formula is <255 and it
works on identical files. Can't figure out why it
fails.
-----Original Message-----
What happens if you use?

Dim myForm As String

myForm = "=((SUM(((" & dataBk2 & "$C11:$C" & lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" & dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C" & _
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & "))) ^ (1/5)-1)*100"

ActiveCell.Formula = myForm

Though don't you really want

ActiveCell.FormulaArray ??

HTH,
Bernie
MS Excel MVP

Hi,

I am running a looping process that pastes a formula
array into a wbk from data in another wbk. The
underlying books are all based on the same template, just
data for different months. After the data is created, I
save the book and save it also as X.xls to shorten the
name (to keep under 255). This formula array worked over
1000 times. All of a sudden I get to a month where I am
getting the Runtime error -1004. The length of the
formula is 196 char

VBA
ActiveCell.Formula = "=((SUM(((" & dataBk2 & "$C11:$C" &
lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" &
dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C" &
_
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & "))) ^
(1/5)-1)*100"

ACTUAL RESULT STRING
=((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))*(([x.xls]S1!$AN11:$AN1509)))/SUM
((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1! $H11:$H1509=500))
*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100

If I take the above formula and paste it into the formula
bar and hit ctrl-shft-entr it does work and returns the
correct value.

So, if it's not the 255 char limit problem, I can't seem
to figure out why it works for so many months prior, and
this file is identical in layout that it it would
suddenly not work. There are no errors in the underlying
value. I'm going bonkers b/c all the files are the
same. WHAT CAN I MISSING????? HELP!!!!

TIA,
Kohai

ps - sorry for the lengthy question.
Click to expand...


.
Click to expand...
Click to expand...
 
Charles, Thx. I did not know that. But that still
doesn't explain why it worked for many, many identical
files earlier. It always looks at X.xls which overwrites
the month being processed and the row/col references
don't change either. It doesn't make sense.
-----Original Message-----
Excel actually converts to R1C1 format before doing the insert, and R1C1
tends to be longer.
I suspect that you actually are hitting the 255 character limit in R1C1 mode

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Bernie,

Thx for responding. It needs to be an array since it is
using multiple criteria (what's in C and H) to sum the
two columns AN & AO. Eventually, I'm going to need to
add a 3rd criteria, so I think I'm going to have to
actually do the math in VBA and have it return the result
rather than use the FormulaArray. That 255 char limit is
a real stinker. But the current formula is <255 and it
works on identical files. Can't figure out why it
fails.
-----Original Message-----
What happens if you use?

Dim myForm As String

myForm = "=((SUM(((" & dataBk2 & "$C11:$C" & lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" & dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2
Click to expand...
& "$C11:$C"
& _
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow
Click to expand...
& ")))
^ (1/5)-1)*100"
ActiveCell.Formula = myForm

Though don't you really want

ActiveCell.FormulaArray ??

HTH,
Bernie
MS Excel MVP

Hi,

I am running a looping process that pastes a formula
array into a wbk from data in another wbk. The
underlying books are all based on the same template, just
data for different months. After the data is
Click to expand...
created,
I
save the book and save it also as X.xls to shorten the
name (to keep under 255). This formula array worked over
1000 times. All of a sudden I get to a month where I am
getting the Runtime error -1004. The length of the
formula is 196 char

VBA
ActiveCell.Formula = "=((SUM(((" & dataBk2
Click to expand...
& "$C11:$C"
&
lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))* ((" &
dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C" &
_
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow
Click to expand...
& ")))
^
(1/5)-1)*100"

ACTUAL RESULT STRING
=((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))*(([x.xls]S1!$AN11:$AN1509)))/SUM
((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1! $H11:$H1509=500))
*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100

If I take the above formula and paste it into the formula
bar and hit ctrl-shft-entr it does work and returns the
correct value.

So, if it's not the 255 char limit problem, I can't seem
to figure out why it works for so many months prior, and
this file is identical in layout that it it would
suddenly not work. There are no errors in the underlying
value. I'm going bonkers b/c all the files are the
same. WHAT CAN I MISSING????? HELP!!!!

TIA,
Kohai

ps - sorry for the lengthy question.


.
Click to expand...
Click to expand...


.
Click to expand...
 
I already checked it 222 is the length in R1C1

--
Regards,
Tom Ogilvy


Charles Williams said:
Excel actually converts to R1C1 format before doing the insert, and R1C1
tends to be longer.
I suspect that you actually are hitting the 255 character limit in R1C1 mode

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Bernie,

Thx for responding. It needs to be an array since it is
using multiple criteria (what's in C and H) to sum the
two columns AN & AO. Eventually, I'm going to need to
add a 3rd criteria, so I think I'm going to have to
actually do the math in VBA and have it return the result
rather than use the FormulaArray. That 255 char limit is
a real stinker. But the current formula is <255 and it
works on identical files. Can't figure out why it
fails.
-----Original Message-----
What happens if you use?

Dim myForm As String

myForm = "=((SUM(((" & dataBk2 & "$C11:$C" & lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" & dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C" & _
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & "))) ^ (1/5)-1)*100"

ActiveCell.Formula = myForm

Though don't you really want

ActiveCell.FormulaArray ??

HTH,
Bernie
MS Excel MVP

Hi,

I am running a looping process that pastes a formula
array into a wbk from data in another wbk. The
underlying books are all based on the same template, just
data for different months. After the data is created, I
save the book and save it also as X.xls to shorten the
name (to keep under 255). This formula array worked over
1000 times. All of a sudden I get to a month where I am
getting the Runtime error -1004. The length of the
formula is 196 char

VBA
ActiveCell.Formula = "=((SUM(((" & dataBk2 & "$C11:$C" &
lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" &
dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C" &
_
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & "))) ^
(1/5)-1)*100"

ACTUAL RESULT STRING
=((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))*(([x.xls]S1!$AN11:$AN1509)))/SUM
((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1! $H11:$H1509=500))
*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100

If I take the above formula and paste it into the formula
bar and hit ctrl-shft-entr it does work and returns the
correct value.

So, if it's not the 255 char limit problem, I can't seem
to figure out why it works for so many months prior, and
this file is identical in layout that it it would
suddenly not work. There are no errors in the underlying
value. I'm going bonkers b/c all the files are the
same. WHAT CAN I MISSING????? HELP!!!!

TIA,
Kohai

ps - sorry for the lengthy question.


.
Click to expand...
Click to expand...
Click to expand...
 
A lot of things don't make sense.

An easy work around for now (to stay is sheet formulas rather than
converting to VB functions) is to enter the first array formula SUM into one
cell, the second array formula SUM into another cell, and do the division
and power in a third cell. (or do the formulas in the same cells, stepwise,
using the result from the first as a constant in the second...)

HTH,
Bernie
MS Excel MVP
 
I made it 254 in R1C1 ...

Tom Ogilvy said:
I already checked it 222 is the length in R1C1

--
Regards,
Tom Ogilvy


Charles Williams said:
Excel actually converts to R1C1 format before doing the insert, and R1C1
tends to be longer.
I suspect that you actually are hitting the 255 character limit in R1C1 mode

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Bernie,

Thx for responding. It needs to be an array since it is
using multiple criteria (what's in C and H) to sum the
two columns AN & AO. Eventually, I'm going to need to
add a 3rd criteria, so I think I'm going to have to
actually do the math in VBA and have it return the result
rather than use the FormulaArray. That 255 char limit is
a real stinker. But the current formula is <255 and it
works on identical files. Can't figure out why it
fails.
-----Original Message-----
What happens if you use?

Dim myForm As String

myForm = "=((SUM(((" & dataBk2 & "$C11:$C" & lrow
& "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*(("
& dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C"
& _
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & ")))
^ (1/5)-1)*100"

ActiveCell.Formula = myForm

Though don't you really want

ActiveCell.FormulaArray ??

HTH,
Bernie
MS Excel MVP

message
Hi,

I am running a looping process that pastes a formula
array into a wbk from data in another wbk. The
underlying books are all based on the same template,
just
data for different months. After the data is created,
I
save the book and save it also as X.xls to shorten the
name (to keep under 255). This formula array worked
over
1000 times. All of a sudden I get to a month where I
am
getting the Runtime error -1004. The length of the
formula is 196 char

VBA
ActiveCell.Formula = "=((SUM(((" & dataBk2 & "$C11:$C"
&
lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" &
dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2
& "$C11:$C" &
_
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & ")))
^
(1/5)-1)*100"

ACTUAL RESULT STRING
=((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))*(([x.xls]S1!$AN11:$AN1509)))/SUM
((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))
*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100

If I take the above formula and paste it into the
formula
bar and hit ctrl-shft-entr it does work and returns the
correct value.

So, if it's not the 255 char limit problem, I can't
seem
to figure out why it works for so many months prior,
and
this file is identical in layout that it it would
suddenly not work. There are no errors in the
underlying
value. I'm going bonkers b/c all the files are the
same. WHAT CAN I MISSING????? HELP!!!!

TIA,
Kohai

ps - sorry for the lengthy question.


.
Click to expand...
Click to expand...
Click to expand...
 
I get 244 by pasting it in in A1 style and then changing options to R1C1
(using both Len in the worksheet and in VBA)

sStr1 =
"=((SUM((([x.xls]S1!R[10]C3:R[1508]C3=R[9]C[-5])*([x.xls]S1!R[10]C8:R[1508]C
8=500))*(([x.xls]S1!R[10]C40:R[1508]C40)))/SUM((([x.xls]S1!R[10]C3:R[1508]C3
=R[9]C[-5])*([x.xls]S1!R[10]C8:R[1508]C8=500))*([x.xls]S1!R[10]C41:R[1508]C4
1)))^(1/5)-1)*100"
? len(sStr1)
244

or using the original formula and formulaconvert:

sStr =
"((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!$H11:$H1509=500))*(([x.xls]S1
!$AN11:$AN1509)))/SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!$H11:$H1509=50
0))*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100"
? len(application.ConvertFormula(sStr,xlA1,xlR1C1))
237

? len(application.ConvertFormula(sStr,xlA1,xlR1C1,xlabsolute))
211

--
Regards,
Tom Ogilvy


Charles Williams said:
I made it 254 in R1C1 ...

Tom Ogilvy said:
I already checked it 222 is the length in R1C1

--
Regards,
Tom Ogilvy


Charles Williams said:
Excel actually converts to R1C1 format before doing the insert, and R1C1
tends to be longer.
I suspect that you actually are hitting the 255 character limit in R1C1 mode

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Bernie,

Thx for responding. It needs to be an array since it is
using multiple criteria (what's in C and H) to sum the
two columns AN & AO. Eventually, I'm going to need to
add a 3rd criteria, so I think I'm going to have to
actually do the math in VBA and have it return the result
rather than use the FormulaArray. That 255 char limit is
a real stinker. But the current formula is <255 and it
works on identical files. Can't figure out why it
fails.
-----Original Message-----
What happens if you use?

Dim myForm As String

myForm = "=((SUM(((" & dataBk2 & "$C11:$C" & lrow
& "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*(("
& dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C"
& _
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & ")))
^ (1/5)-1)*100"

ActiveCell.Formula = myForm

Though don't you really want

ActiveCell.FormulaArray ??

HTH,
Bernie
MS Excel MVP

message
Hi,

I am running a looping process that pastes a formula
array into a wbk from data in another wbk. The
underlying books are all based on the same template,
just
data for different months. After the data is created,
I
save the book and save it also as X.xls to shorten the
name (to keep under 255). This formula array worked
over
1000 times. All of a sudden I get to a month where I
am
getting the Runtime error -1004. The length of the
formula is 196 char

VBA
ActiveCell.Formula = "=((SUM(((" & dataBk2 & "$C11:$C"
&
lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" &
dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2
& "$C11:$C" &
_
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & ")))
^
(1/5)-1)*100"

ACTUAL RESULT STRING
=((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))*(([x.xls]S1!$AN11:$AN1509)))/SUM
((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))
*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100

If I take the above formula and paste it into the
formula
bar and hit ctrl-shft-entr it does work and returns the
correct value.

So, if it's not the 255 char limit problem, I can't
seem
to figure out why it works for so many months prior,
and
this file is identical in layout that it it would
suddenly not work. There are no errors in the
underlying
value. I'm going bonkers b/c all the files are the
same. WHAT CAN I MISSING????? HELP!!!!

TIA,
Kohai

ps - sorry for the lengthy question.


.
Click to expand...
Click to expand...
Click to expand...
 
I used the 244 method so probably I mistook a number and it actually was
244...

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Tom Ogilvy said:
I get 244 by pasting it in in A1 style and then changing options to R1C1
(using both Len in the worksheet and in VBA)

sStr1 =
"=((SUM((([x.xls]S1!R[10]C3:R[1508]C3=R[9]C[-5])*([x.xls]S1!R[10]C8:R[1508]C
8=500))*(([x.xls]S1!R[10]C40:R[1508]C40)))/SUM((([x.xls]S1!R[10]C3:R[1508]C3
=R[9]C[-5])*([x.xls]S1!R[10]C8:R[1508]C8=500))*([x.xls]S1!R[10]C41:R[1508]C4
1)))^(1/5)-1)*100"
? len(sStr1)
244

or using the original formula and formulaconvert:

sStr =
"((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!$H11:$H1509=500))*(([x.xls]S1
!$AN11:$AN1509)))/SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!$H11:$H1509=50
0))*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100"
? len(application.ConvertFormula(sStr,xlA1,xlR1C1))
237

? len(application.ConvertFormula(sStr,xlA1,xlR1C1,xlabsolute))
211

--
Regards,
Tom Ogilvy


Charles Williams said:
I made it 254 in R1C1 ...

Tom Ogilvy said:
I already checked it 222 is the length in R1C1

--
Regards,
Tom Ogilvy


Excel actually converts to R1C1 format before doing the insert, and R1C1
tends to be longer.
I suspect that you actually are hitting the 255 character limit in
R1C1
mode

Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

Bernie,

Thx for responding. It needs to be an array since it is
using multiple criteria (what's in C and H) to sum the
two columns AN & AO. Eventually, I'm going to need to
add a 3rd criteria, so I think I'm going to have to
actually do the math in VBA and have it return the result
rather than use the FormulaArray. That 255 char limit is
a real stinker. But the current formula is <255 and it
works on identical files. Can't figure out why it
fails.
-----Original Message-----
What happens if you use?

Dim myForm As String

myForm = "=((SUM(((" & dataBk2 & "$C11:$C" & lrow
& "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*(("
& dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2 & "$C11:$C"
& _
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & ")))
^ (1/5)-1)*100"

ActiveCell.Formula = myForm

Though don't you really want

ActiveCell.FormulaArray ??

HTH,
Bernie
MS Excel MVP

message
Hi,

I am running a looping process that pastes a formula
array into a wbk from data in another wbk. The
underlying books are all based on the same template,
just
data for different months. After the data is created,
I
save the book and save it also as X.xls to shorten the
name (to keep under 255). This formula array worked
over
1000 times. All of a sudden I get to a month where I
am
getting the Runtime error -1004. The length of the
formula is 196 char

VBA
ActiveCell.Formula = "=((SUM(((" & dataBk2 & "$C11:$C"
&
lrow & "=C10)*(" & _
dataBk2 & "$H11:$H" & lrow & "=" & LTGIndex & "))*((" &
dataBk2 & _
"$AN11:$AN" & lrow & ")))/SUM(((" & dataBk2
& "$C11:$C" &
_
lrow & "=C10)*(" & dataBk2 & "$H11:$H" & lrow & "=" & _
LTGIndex & "))*(" & dataBk2 & "$AO11:$AO" & lrow & ")))
^
(1/5)-1)*100"

ACTUAL RESULT STRING
=((SUM((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))*(([x.xls]S1!$AN11:$AN1509)))/SUM
((([x.xls]S1!$C11:$C1509=C10)*([x.xls]S1!
$H11:$H1509=500))
*([x.xls]S1!$AO11:$AO1509)))^(1/5)-1)*100

If I take the above formula and paste it into the
formula
bar and hit ctrl-shft-entr it does work and returns the
correct value.

So, if it's not the 255 char limit problem, I can't
seem
to figure out why it works for so many months prior,
and
this file is identical in layout that it it would
suddenly not work. There are no errors in the
underlying
value. I'm going bonkers b/c all the files are the
same. WHAT CAN I MISSING????? HELP!!!!

TIA,
Kohai

ps - sorry for the lengthy question.


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top