| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Error
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
Range("K13").HasArray = true
"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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
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! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formula Error
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF formula returns error; abbreviating the formula | Excel Discussion (Misc queries) | |||
| Formula Error-Error Message | Excel Programming | |||
| Formula expected end of statement error, typing formula into cell as part of VBA macro | Excel Programming | |||
| How do I replace "#N/A" error, to continue my formula w/o error? | Excel Worksheet Functions | |||
| Formula error with Mac resulting in '#NAME' error | Excel Programming | |||
Hybrid Mode
