Formula Error

A

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!
 
J

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
 
A

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

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

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


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...
Click to expand...
 
A

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.

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...
Click to expand...
 
J

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


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...
Click to expand...
 
A

Al

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...
Click to expand...
 
A

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?

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...
Click to expand...
 
J

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


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...
Click to expand...
 
A

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



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...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Insert long formula 3
Formula Help within Code 1
Sumproduct gives #value 6
Open CSV File 3
Paste formula 4
create macro - delete all rows with zero in column L 2
Array VLOOKUP lookup_Value does not increment 3
is Retrieving a Cell address from a cell containing a SUMPRODUCTformula a problem ? 5

Top