I dont think so as the last corrected formula (which I posted) with 5 worked
for me...

Range("K13").FormulaArray = "=SUM(IF(ISERROR(MATCH($C$14:$C$500," & _
"'G:\MyPath\[MyFile.xls]Sheet4'!$A$1:$A$500,0)),IF($F$14:$F$500" & _
"=""7-F"",IF($I$14:$I$500<"""",IF($J$14:$J$500=""A"",IF (K14:K500" & _
"<""N/A"",ROUNDUP(1.02*(K$14:K$500),0)))))))"

If this post helps click Yes
---------------
Jacob Skaria


"Al" wrote:

It appears as though having 5 IF's that is causing the problem. I can remove
any one of the 5 and the code runs. Add it back and it fails. Any ideas?

"Jacob Skaria" wrote:

OK Did you tried the one in my last post...which I think ishaving the extra
condition..(that you added)
--
If this post helps click Yes
---------------
Jacob Skaria


"Al" wrote:

Thats what I did...I added the path bit to the original formula and it
worked. I changed criteria and it still worked. I added the 5th IF and it
failed. The formula works as a formula manually entered it only fails when
entered via code.

"Jacob Skaria" wrote:

I meant to replace c:\book1 etc; in my earlier formula to the actual ones...I
dont find any issue with the below code either.. provided you have the file
in the correct location...and 'sheet4' exists...

Range("K13").FormulaArray = "=SUM(IF(ISERROR(MATCH($C$14:$C$500," & _
"'G:\MyPath\[MyFile.xls]Sheet4'!$A$1:$A$500,0)),IF($F$14:$F$500" & _
"=""7-F"",IF($I$14:$I$500<"""",IF($J$14:$J$500=""A"",IF (K14:K500" & _
"<""N/A"",ROUNDUP(1.02*(K$14:K$500),0)))))))"

If this post helps click Yes
---------------
Jacob Skaria


"Al" wrote:

Im not sure what to replace it with...I took one of the other formula
variants (same path, different criteria) path stays the same other criteria
changes, and it works as a formula entered in the spreadsheet. Its only when
I put it in the code and change " to "" that it fails.

This line works

Range("K13").FormulaArray =
"=SUM(IF(ISERROR(MATCH($C$14:$C$500,'G:\MyPath \[MyFile.xls]Sheet4'!$A$1:$A$500,0)),IF($F$14:$F$500=""7-F"",IF($I$14:$I$500<"""",IF($J$14:$J$500=""A"",IF (K14:K500<""N/A"",ROUNDUP(1.02*(K$14:K$500),0)))))))"

I add another IF to it and change the criteria and it fails...

"Jacob Skaria" wrote:

Hi Again

With a file as c:\book3.xls with sheet1..the below works for me...Try
replacing the file path, file name and sheetname.

ActiveCell.FormulaArray = "=SUM(IF(ISERROR(MATCH($C$14:$C$500,'c:\" & _
"[Book3.xls]Sheet1'!$A$1:$A$500,0)),IF($F$14:$F$500=""7-F"",IF" & _
"($I$14:$I$500=""Roger"",IF($E$14:$e$500=""Widget" ",IF($J$14:$J$500" & _
"=""s"",IF(K14:K500<""N/A"",ROUNDUP(1.02*(K$14:K$500),0))))))))"

If this post helps click Yes
---------------
Jacob Skaria


"Al" wrote:

Thanks to Jacob Skaria who go me to this point with this issue. It started
as a formula question and then moved to VBA. Im still having problems so I
am moving it here. I am trying to enter the array formula:

=SUM(IF(ISERROR(MATCH($C$14:$C$500,'G:\MyPath\[MyFile.xls]Sheet4'!$A$1:$A$500,0)),IF($F$14:$F$500="7-F",IF($I$14:$I$500="Roger",IF($E$14:$e$500="Widget ",IF($J$14:$J$500="s",IF(K14:K500<"N/A",ROUNDUP(1.02*(K$14:K$500),0))))))))"

If I enter the formuala (Ctrl+shift+enter) it works. However when in my
code I use

Range("K13").FormulaArray =
"=SUM(IF(ISERROR(MATCH($C$14:$C$500,'G:\MyPath \[MyFile.xls]Sheet4'!$A$1:$A$500,0)),IF($F$14:$F$500=""7-F"",IF($I$14:$I$500=""Roger"",IF($E$14:$e$500=""Wi dget"",IF($J$14:$J$500=""s"",IF(K14:K500<""N/A"",ROUNDUP(1.02*(K$14:K$500),0))))))))"

in my code, I get "Unable to set FormulaArray Property of the
range class".

This is a variant of another formula in the code with an added IF, and the
other formula works when the code is executed. I have copied the formula and
inserted it in
Range("K13").FormulaArray = "Formula" and replaced the single quotes with
double quotes. What am I doing wrong?

Thanks!