Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and FormulaArray Runtime Err <is not more than 255 Char
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and FormulaArray Runtime Err <is not more than 255 Char
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 "Kohai" wrote in 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA and FormulaArray Runtime Err <is not more than 255 Char
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 "Kohai" wrote in 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND 1 char in cell of any 3 char =True | Excel Discussion (Misc queries) | |||
8500 cells with phone number(7 char.), wishing to add area code (10 char.) | Excel Discussion (Misc queries) | |||
FormulaArray | Excel Worksheet Functions | |||
FormulaArray..... HELP !!! | Excel Programming | |||
FormulaArray | Excel Programming |