Formula Error

Al · Oct 9, 2009

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=""Widget"",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!

Joel · Oct 9, 2009

Range("K13").HasArray = true

Jacob Skaria · Oct 9, 2009

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

Al · Oct 9, 2009

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 said:
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 said:

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=""Widget"",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!

Click to expand...

Jacob Skaria · Oct 9, 2009

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 said:
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 said:

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 said:

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=""Widget"",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!

Click to expand...

Click to expand...

Al · Oct 9, 2009

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 said:
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 said:

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 said:

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

:

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=""Widget"",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!

Click to expand...

Click to expand...

Jacob Skaria · Oct 9, 2009

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 said:
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 said:

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 said:

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...

:

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

:

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=""Widget"",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!

Click to expand...

Click to expand...

Al · Oct 9, 2009

I'm still working on it, but why do you concatenate pieces of the formula?

Jacob Skaria said:
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 said:

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 said:

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

:

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...

:

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

:

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=""Widget"",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!

Click to expand...

Click to expand...

Al · Oct 9, 2009

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 said:
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 said:

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 said:

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

:

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...

:

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

:

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=""Widget"",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!

Click to expand...

Click to expand...

Jacob Skaria · Oct 9, 2009

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 said:
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 said:

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 said:

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.

:

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

:

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...

:

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

:

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=""Widget"",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!

Click to expand...

Click to expand...

Al · Oct 9, 2009

Ok 2 Problems
Problem 1) I can't count...
5 IF's work-
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)))))))"

6 IF's Don't work-

Range("K13").FormulaArray = "=SUM(IF(ISERROR(MATCH($C$14:$C$500," &
"'G:\Billing\Network\[NetworkReservations.xls]Sheet4'!$A$1:$A$500,0)),IF($D$14:$D$500=""7-F"",IF($E$14:$E$500"
& "=""Roger"",IF($F$14:$F$500<>""Widget"",IF($J$14:$J$500=""S"",IF(K14:K500"
& "<>""N/A"",ROUNDUP(1.02*(K$14:K$500),0)))))))"

Problem 2) I need a drink and I'm still at the office! This is driving me
crazy

Jacob Skaria said:
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 said:

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 said:

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

:

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.

:

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

:

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...

:

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

:

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=""Widget"",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!

Click to expand...

Click to expand...

Formula Error

Al

Joel

Jacob Skaria

Al

Jacob Skaria

Al

Jacob Skaria

Al

Al

Jacob Skaria

Al