Formula Error

[Al]

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]

Range("K13").HasArray = true

 

[Jacob Skaria]

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]

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!

 

[Jacob Skaria]

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!

 

[Al]

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!

 

[Jacob Skaria]

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!

 

[Al]

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

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!

 

[Al]

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?

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!

 

[Jacob Skaria]

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

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?

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!

 

[Al]

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

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

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?

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!