|
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. |
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. |
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. . |
VBA and FormulaArray Runtime Err <is not more than 255 Char
I made it 254 in R1C1 ...
"Tom Ogilvy" wrote in message ... I already checked it 222 is the length in R1C1 -- Regards, Tom Ogilvy "Charles Williams" wrote in 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 wrote in message ... 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. . |
VBA and FormulaArray Runtime Err <is not more than 255 Char
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,xl absolute)) 211 -- Regards, Tom Ogilvy "Charles Williams" wrote in message ... I made it 254 in R1C1 ... "Tom Ogilvy" wrote in message ... I already checked it 222 is the length in R1C1 -- Regards, Tom Ogilvy "Charles Williams" wrote in 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 wrote in message ... 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. . |
VBA and FormulaArray Runtime Err <is not more than 255 Char
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" wrote in message ... 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,xl absolute)) 211 -- Regards, Tom Ogilvy "Charles Williams" wrote in message ... I made it 254 in R1C1 ... "Tom Ogilvy" wrote in message ... I already checked it 222 is the length in R1C1 -- Regards, Tom Ogilvy "Charles Williams" wrote in 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 wrote in message ... 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. . |
| All times are GMT +1. The time now is 12:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com