Array formula in a makro want work

[Guest]

Hi,
I have this formulae:
=IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<>"",IF(Need!$G$8:Need!$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(Need!$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

 

[Ron Rosenfeld]

Hi,
I have this formulae:
=IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<>"",IF(Need!$G$8:Need!$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(Need!$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

Take a look at HELP for the FormulaArray Property. Your formula will need to
use the R1C1 reference style.
--ron

 

[Guest]

Hi Ron,
Thanks for your answer, but:
"=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<>"",IF(Need!R8C7:Need!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:Need!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]

Hi Ron,
Thanks for your answer, but:
"=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<>"",IF(Need!R8C7:Need!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:Need!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

 

[Guest]

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

Hi Ron,
Thanks for your answer, but:
"=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<>"",IF(Need!R8C7:Need!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:Need!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

Take a look at HELP for the FormulaArray Property. Your formula will need to
use the R1C1 reference style.
--ron

 

[Ron Rosenfeld]

Hi Ron,
Thanks for your answer, but:
"=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<>"",IF(Need!R8C7:Need!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:Need!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