| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array formula in a makro want work
Hi,
I have this formulae: =IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<"",IF(Need!$G$8:Nee d!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)),IF(Need!$E$8:Need!$E$21<$B$4,IF(Need!$E$ 8:Need!$E$21<$B$3,IF(Need!$E$8:Need!$E$21<$B$2,I F(Need!$E$8:Need!$E$21<$B$1,Need!T$8:Need!T$21,0) ,0),0),0)))),SUM(IF(Need!$F$8:Need!$F$21="",IF(Nee d!$G$8:Need!$G$21=$D10,IF(Need!$E$8:Need!$E$21<$B $4,IF(Need!$E$8:Need!$E$21<$B$3,IF(Need!$E$8:Need !$E$21<$B$2,IF(Need!$E$8:Need!$E$21<$B$1,Need!T$ 8:Need!T$21,0),0),0),0))))) I am not able to activate that formula in array {} format. I should be able to so that I get the right results. It is part of a macro and macro does not accept that as an array. I have tried to record that, no acceptance. Problem seems to be in this part: IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10) When activating cell content and with shift+ctrl+enter it will be an array and will give right results. How could I resolve this problem? Thanks in advance MakeLei |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array formula in a makro want work
On Sun, 26 Aug 2007 22:34:15 -0700, Makelei
wrote: Hi, I have this formulae: =IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<"",IF(Need!$G$8:Nee d!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)),IF(Need!$E$8:Need!$E$21<$B$4,IF(Need!$E $8:Need!$E$21<$B$3,IF(Need!$E$8:Need!$E$21<$B$2, IF(Need!$E$8:Need!$E$21<$B$1,Need!T$8:Need!T$21,0 ),0),0),0)))),SUM(IF(Need!$F$8:Need!$F$21="",IF(Ne ed!$G$8:Need!$G$21=$D10,IF(Need!$E$8:Need!$E$21<$ B$4,IF(Need!$E$8:Need!$E$21<$B$3,IF(Need!$E$8:Nee d!$E$21<$B$2,IF(Need!$E$8:Need!$E$21<$B$1,Need!T $8:Need!T$21,0),0),0),0))))) I am not able to activate that formula in array {} format. I should be able to so that I get the right results. It is part of a macro and macro does not accept that as an array. I have tried to record that, no acceptance. Problem seems to be in this part: IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10) When activating cell content and with shift+ctrl+enter it will be an array and will give right results. How could I resolve this problem? Thanks in advance MakeLei Take a look at HELP for the FormulaArray Property. Your formula will need to use the R1C1 reference style. --ron |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array formula in a makro want work
Hi Ron,
Thanks for your answer, but: "=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<"",IF(Need!R8C7:Nee d!R21C7=RIGHT(RC4,LEN(RC4)-SEARCH(" ",RC4)),IF(Need!R8C5:Need!R21C5<R4C2,IF(Need!R8C5 :Need!R21C5<R3C2,IF(Need!R8C5:Need!R21C5<R2C2,IF (Need!R8C5:Need!R21C5<R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))),SUM(IF(Need!R8C6:Need!R21C6="",IF( Need!R8C7:Need!R21C7=RC4,IF(Need!R8C5:Need!R21C5< R4C2,IF(Need!R8C5:Need!R21C5<R3C2,IF(Need!R8C5:Ne ed!R21C5<R2C2,IF(Need!R8C5:Need!R21C5<R1C2,Need! R8C[14]:Need!R21C[14],0),0),0),0)))))" Does not work either... Problem seems to be in ths bit: IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10) Any proposals? BR MakeLei "Ron Rosenfeld" wrote: On Sun, 26 Aug 2007 22:34:15 -0700, Makelei wrote: Hi, I have this formulae: =IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<"",IF(Need!$G$8:Nee d!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)),IF(Need!$E$8:Need!$E$21<$B$4,IF(Need!$E $8:Need!$E$21<$B$3,IF(Need!$E$8:Need!$E$21<$B$2, IF(Need!$E$8:Need!$E$21<$B$1,Need!T$8:Need!T$21,0 ),0),0),0)))),SUM(IF(Need!$F$8:Need!$F$21="",IF(Ne ed!$G$8:Need!$G$21=$D10,IF(Need!$E$8:Need!$E$21<$ B$4,IF(Need!$E$8:Need!$E$21<$B$3,IF(Need!$E$8:Nee d!$E$21<$B$2,IF(Need!$E$8:Need!$E$21<$B$1,Need!T $8:Need!T$21,0),0),0),0))))) I am not able to activate that formula in array {} format. I should be able to so that I get the right results. It is part of a macro and macro does not accept that as an array. I have tried to record that, no acceptance. Problem seems to be in this part: IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10) When activating cell content and with shift+ctrl+enter it will be an array and will give right results. How could I resolve this problem? Thanks in advance MakeLei Take a look at HELP for the FormulaArray Property. Your formula will need to use the R1C1 reference style. --ron |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array formula in a makro want work
On Mon, 27 Aug 2007 04:18:00 -0700, Makelei
wrote: Hi Ron, Thanks for your answer, but: "=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<"",IF(Need!R8C7:Nee d!R21C7=RIGHT(RC4,LEN(RC4)-SEARCH(" ",RC4)),IF(Need!R8C5:Need!R21C5<R4C2,IF(Need!R8C 5:Need!R21C5<R3C2,IF(Need!R8C5:Need!R21C5<R2C2,I F(Need!R8C5:Need!R21C5<R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))),SUM(IF(Need!R8C6:Need!R21C6="",IF( Need!R8C7:Need!R21C7=RC4,IF(Need!R8C5:Need!R21C5< R4C2,IF(Need!R8C5:Need!R21C5<R3C2,IF(Need!R8C5:Ne ed!R21C5<R2C2,IF(Need!R8C5:Need!R21C5<R1C2,Need! R8C[14]:Need!R21C[14],0),0),0),0)))))" Does not work either... Problem seems to be in ths bit: IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10) Any proposals? BR MakeLei What does "does not work" mean? It speeds up the trouble shooting process if you are specific about the problems you are having, rather than just saying "does not work". For one thing, your syntax is incorrect. Take the beginning: "=IF(LEFT(RC4,4)="Ext-",SUM(IF(N ... If you are including quote marks (") within the quotation, you need to double them. VB will interpret a single quote as the beginning or end of a string. "=IF(LEFT(RC4,4)=""Ext-"",SUM(IF(N ... --ron |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array formula in a makro want work
http://support.microsoft.com/kb/213181/en-us
Run-time error using FormulaArray property Your formula is well over 400 characters long. -- Regards, Tom Ogilvy "Makelei" wrote: Hi Ron, Thanks for your answer, but: "=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<"",IF(Need!R8C7:Nee d!R21C7=RIGHT(RC4,LEN(RC4)-SEARCH(" ",RC4)),IF(Need!R8C5:Need!R21C5<R4C2,IF(Need!R8C5 :Need!R21C5<R3C2,IF(Need!R8C5:Need!R21C5<R2C2,IF (Need!R8C5:Need!R21C5<R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))),SUM(IF(Need!R8C6:Need!R21C6="",IF( Need!R8C7:Need!R21C7=RC4,IF(Need!R8C5:Need!R21C5< R4C2,IF(Need!R8C5:Need!R21C5<R3C2,IF(Need!R8C5:Ne ed!R21C5<R2C2,IF(Need!R8C5:Need!R21C5<R1C2,Need! R8C[14]:Need!R21C[14],0),0),0),0)))))" Does not work either... Problem seems to be in ths bit: IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10) Any proposals? BR MakeLei "Ron Rosenfeld" wrote: On Sun, 26 Aug 2007 22:34:15 -0700, Makelei wrote: Hi, I have this formulae: =IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<"",IF(Need!$G$8:Nee d!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)),IF(Need!$E$8:Need!$E$21<$B$4,IF(Need!$E $8:Need!$E$21<$B$3,IF(Need!$E$8:Need!$E$21<$B$2, IF(Need!$E$8:Need!$E$21<$B$1,Need!T$8:Need!T$21,0 ),0),0),0)))),SUM(IF(Need!$F$8:Need!$F$21="",IF(Ne ed!$G$8:Need!$G$21=$D10,IF(Need!$E$8:Need!$E$21<$ B$4,IF(Need!$E$8:Need!$E$21<$B$3,IF(Need!$E$8:Nee d!$E$21<$B$2,IF(Need!$E$8:Need!$E$21<$B$1,Need!T $8:Need!T$21,0),0),0),0))))) I am not able to activate that formula in array {} format. I should be able to so that I get the right results. It is part of a macro and macro does not accept that as an array. I have tried to record that, no acceptance. Problem seems to be in this part: IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10) When activating cell content and with shift+ctrl+enter it will be an array and will give right results. How could I resolve this problem? Thanks in advance MakeLei Take a look at HELP for the FormulaArray Property. Your formula will need to use the R1C1 reference style. --ron |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Array formula in a makro want work
On Mon, 27 Aug 2007 04:18:00 -0700, Makelei
wrote: Hi Ron, Thanks for your answer, but: "=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<"",IF(Need!R8C7:Nee d!R21C7=RIGHT(RC4,LEN(RC4)-SEARCH(" ",RC4)),IF(Need!R8C5:Need!R21C5<R4C2,IF(Need!R8C 5:Need!R21C5<R3C2,IF(Need!R8C5:Need!R21C5<R2C2,I F(Need!R8C5:Need!R21C5<R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))),SUM(IF(Need!R8C6:Need!R21C6="",IF( Need!R8C7:Need!R21C7=RC4,IF(Need!R8C5:Need!R21C5< R4C2,IF(Need!R8C5:Need!R21C5<R3C2,IF(Need!R8C5:Ne ed!R21C5<R2C2,IF(Need!R8C5:Need!R21C5<R1C2,Need! R8C[14]:Need!R21C[14],0),0),0),0)))))" Does not work either... Problem seems to be in ths bit: IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10) Any proposals? BR MakeLei There is another potential problem and I don't know how to solve it. (Again, it would have been easier had you defined "does not work"). At least in excel 2003 and earlier, you cannot pass a formula that is longer than 255 characters. See http://support.microsoft.com/kb/213181/EN-US/ --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't seem to get my Array formula to work................ | Excel Discussion (Misc queries) | |||
| array formula does not work | Excel Worksheet Functions | |||
| Formula or makro help | Excel Programming | |||
| Will an Array Formula work here? | Excel Discussion (Misc queries) | |||
| old makro (excel 95) should work with vba-modules | Excel Programming | |||
Hybrid Mode
