Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
I have the following code that gives me a #N/A for some cells. I want to
change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
Not sure where you are picking up the error, but it would appear that it is
in the latter part of the formula. If so, then try, wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",IF(ISNA(INDEX(CWWType, MATCH(f$5,CWWDate,0),1)),"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1))" Not tested though. Else, you can insert the ISNA bit at the beginning, and say wsPh.Range("f6:AD6").Formula = "=IF(ISNA(F$5,"""",IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x""))" -- HTH Kassie Replace xxx with hotmail "Tony" wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
I get a 1004 error now. Is this a code format error?
"Kassie" wrote: Not sure where you are picking up the error, but it would appear that it is in the latter part of the formula. If so, then try, wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",IF(ISNA(INDEX(CWWType, MATCH(f$5,CWWDate,0),1)),"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1))" Not tested though. Else, you can insert the ISNA bit at the beginning, and say wsPh.Range("f6:AD6").Formula = "=IF(ISNA(F$5,"""",IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x""))" -- HTH Kassie Replace xxx with hotmail "Tony" wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
Did you enter this in one line? Which one of the two is giving this error?
-- HTH Kassie Replace xxx with hotmail "Tony" wrote: I get a 1004 error now. Is this a code format error? "Kassie" wrote: Not sure where you are picking up the error, but it would appear that it is in the latter part of the formula. If so, then try, wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",IF(ISNA(INDEX(CWWType, MATCH(f$5,CWWDate,0),1)),"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1))" Not tested though. Else, you can insert the ISNA bit at the beginning, and say wsPh.Range("f6:AD6").Formula = "=IF(ISNA(F$5,"""",IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x""))" -- HTH Kassie Replace xxx with hotmail "Tony" wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
It was the second one entered on one line as:
wsPh.Range("f6:AD6").Formula = "=IF(ISNA(F$5,"""",IF(f$5<"""",INDEX(CWWType,MATC H(f$5,CWWDate,0),1),""x""))" "Kassie" wrote: Did you enter this in one line? Which one of the two is giving this error? -- HTH Kassie Replace xxx with hotmail "Tony" wrote: I get a 1004 error now. Is this a code format error? "Kassie" wrote: Not sure where you are picking up the error, but it would appear that it is in the latter part of the formula. If so, then try, wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",IF(ISNA(INDEX(CWWType, MATCH(f$5,CWWDate,0),1)),"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1))" Not tested though. Else, you can insert the ISNA bit at the beginning, and say wsPh.Range("f6:AD6").Formula = "=IF(ISNA(F$5,"""",IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x""))" -- HTH Kassie Replace xxx with hotmail "Tony" wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
Sorry!
Left out the closing bracket! It should read "=IF(ISNA(F$5 and then a closing bracket, before the comma and what follows. -- HTH Kassie Replace xxx with hotmail "Tony" wrote: It was the second one entered on one line as: wsPh.Range("f6:AD6").Formula = "=IF(ISNA(F$5,"""",IF(f$5<"""",INDEX(CWWType,MATC H(f$5,CWWDate,0),1),""x""))" "Kassie" wrote: Did you enter this in one line? Which one of the two is giving this error? -- HTH Kassie Replace xxx with hotmail "Tony" wrote: I get a 1004 error now. Is this a code format error? "Kassie" wrote: Not sure where you are picking up the error, but it would appear that it is in the latter part of the formula. If so, then try, wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",IF(ISNA(INDEX(CWWType, MATCH(f$5,CWWDate,0),1)),"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1))" Not tested though. Else, you can insert the ISNA bit at the beginning, and say wsPh.Range("f6:AD6").Formula = "=IF(ISNA(F$5,"""",IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x""))" -- HTH Kassie Replace xxx with hotmail "Tony" wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
Does this formula do what you want if you enter it manually?
=IF(F$5="","x",IF(ISNA(MATCH($F$5,CWWDate,0)),"", INDEX(CWWType,MATCH(F$5,CWWDate,0),1))) If yes, then try: wsPH.Range("f6:AD6").Formula _ = "=IF(F$5="""",""x"",IF(ISNA(MATCH($F$5,CWWDate,0)) ," _ & """"",INDEX(CWWType,MATCH(F$5,CWWDate,0),1)))" I move the "x" to the front and changed the comparison to equal. It's easier for my brain to see what's going on. Tony wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
Ps. I didn't test this.
I had no idea what was in F5 and what CWWType and CWWDate were. Dave Peterson wrote: Does this formula do what you want if you enter it manually? =IF(F$5="","x",IF(ISNA(MATCH($F$5,CWWDate,0)),"", INDEX(CWWType,MATCH(F$5,CWWDate,0),1))) If yes, then try: wsPH.Range("f6:AD6").Formula _ = "=IF(F$5="""",""x"",IF(ISNA(MATCH($F$5,CWWDate,0)) ," _ & """"",INDEX(CWWType,MATCH(F$5,CWWDate,0),1)))" I move the "x" to the front and changed the comparison to equal. It's easier for my brain to see what's going on. Tony wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
Dave, this did the trick. All I had to do was take out the $ sign just before
the "F" after the first MATCH. "Dave Peterson" wrote: Does this formula do what you want if you enter it manually? =IF(F$5="","x",IF(ISNA(MATCH($F$5,CWWDate,0)),"", INDEX(CWWType,MATCH(F$5,CWWDate,0),1))) If yes, then try: wsPH.Range("f6:AD6").Formula _ = "=IF(F$5="""",""x"",IF(ISNA(MATCH($F$5,CWWDate,0)) ," _ & """"",INDEX(CWWType,MATCH(F$5,CWWDate,0),1)))" I move the "x" to the front and changed the comparison to equal. It's easier for my brain to see what's going on. Tony wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
Kassie, thanks for your help. Dave's helped top finish off what we were
working on. Much appreciated. "Kassie" wrote: Sorry! Left out the closing bracket! It should read "=IF(ISNA(F$5 and then a closing bracket, before the comma and what follows. -- HTH Kassie Replace xxx with hotmail "Tony" wrote: It was the second one entered on one line as: wsPh.Range("f6:AD6").Formula = "=IF(ISNA(F$5,"""",IF(f$5<"""",INDEX(CWWType,MATC H(f$5,CWWDate,0),1),""x""))" "Kassie" wrote: Did you enter this in one line? Which one of the two is giving this error? -- HTH Kassie Replace xxx with hotmail "Tony" wrote: I get a 1004 error now. Is this a code format error? "Kassie" wrote: Not sure where you are picking up the error, but it would appear that it is in the latter part of the formula. If so, then try, wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",IF(ISNA(INDEX(CWWType, MATCH(f$5,CWWDate,0),1)),"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1))" Not tested though. Else, you can insert the ISNA bit at the beginning, and say wsPh.Range("f6:AD6").Formula = "=IF(ISNA(F$5,"""",IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x""))" -- HTH Kassie Replace xxx with hotmail "Tony" wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
Is there anyway of getting an explanation on how this statement is formatted
to appear as a formula in the worksheet cell: wsPH.Range("f6:AD6").Formula _ = "=IF(F$5="""",""x"",IF(ISNA(MATCH($F$5,CWWDate,0)) ," _ & """"",INDEX(CWWType,MATCH(F$5,CWWDate,0),1)))" "Dave Peterson" wrote: Ps. I didn't test this. I had no idea what was in F5 and what CWWType and CWWDate were. Dave Peterson wrote: Does this formula do what you want if you enter it manually? =IF(F$5="","x",IF(ISNA(MATCH($F$5,CWWDate,0)),"", INDEX(CWWType,MATCH(F$5,CWWDate,0),1))) If yes, then try: wsPH.Range("f6:AD6").Formula _ = "=IF(F$5="""",""x"",IF(ISNA(MATCH($F$5,CWWDate,0)) ," _ & """"",INDEX(CWWType,MATCH(F$5,CWWDate,0),1)))" I move the "x" to the front and changed the comparison to equal. It's easier for my brain to see what's going on. Tony wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
I'm not sure I understand the question.
The formula is the same as the manual suggestion. The only difference is how you have to specify doublequotes in code (you double them!). And I used the continuation character (space underscore) so that you don't have to worry about line breaks in the message (when you copy|paste to the code window). ps. Sorry about that extra $ sign. If f5 = "x" then show nothing ("") else if there is no match between F5 and the CWWDate range then show nothing ("") else do the index(match()) end if end if Tony wrote: Is there anyway of getting an explanation on how this statement is formatted to appear as a formula in the worksheet cell: wsPH.Range("f6:AD6").Formula _ = "=IF(F$5="""",""x"",IF(ISNA(MATCH($F$5,CWWDate,0)) ," _ & """"",INDEX(CWWType,MATCH(F$5,CWWDate,0),1)))" "Dave Peterson" wrote: Ps. I didn't test this. I had no idea what was in F5 and what CWWType and CWWDate were. Dave Peterson wrote: Does this formula do what you want if you enter it manually? =IF(F$5="","x",IF(ISNA(MATCH($F$5,CWWDate,0)),"", INDEX(CWWType,MATCH(F$5,CWWDate,0),1))) If yes, then try: wsPH.Range("f6:AD6").Formula _ = "=IF(F$5="""",""x"",IF(ISNA(MATCH($F$5,CWWDate,0)) ," _ & """"",INDEX(CWWType,MATCH(F$5,CWWDate,0),1)))" I move the "x" to the front and changed the comparison to equal. It's easier for my brain to see what's going on. Tony wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
Dave, I am trying to use your explanation for the quotes when specifying
code, but it come up empty: wsPh.Range("f5:AD5").Formula = "=CWW SCHEDULE FOR COLLECTIONS FOR _ & "" "" & MONTH($AE$1) & "" "" & YEAR($AE$1)" Also, how do I format the MONTH as MMMMM instead of MM (i.e. CWW SCHEDULE FOR COLLECTIONS FOR MAY 2009 "Dave Peterson" wrote: Ps. I didn't test this. I had no idea what was in F5 and what CWWType and CWWDate were. Dave Peterson wrote: Does this formula do what you want if you enter it manually? =IF(F$5="","x",IF(ISNA(MATCH($F$5,CWWDate,0)),"", INDEX(CWWType,MATCH(F$5,CWWDate,0),1))) If yes, then try: wsPH.Range("f6:AD6").Formula _ = "=IF(F$5="""",""x"",IF(ISNA(MATCH($F$5,CWWDate,0)) ," _ & """"",INDEX(CWWType,MATCH(F$5,CWWDate,0),1)))" I move the "x" to the front and changed the comparison to equal. It's easier for my brain to see what's going on. Tony wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
iserror to eliminate #N/A
wsph.Range("f5:AD5").Formula = "=""CWW SCHEDULE FOR COLLECTIONS FOR """ _
& "&text($ae$1,""mmmm yyyy"")" or maybe... wsph.Range("f5:AD5").Formula = "=""CWW SCHEDULE FOR COLLECTIONS FOR """ _ & "&upper(text($ae$1,""mmmm yyyy""))" Tony wrote: Dave, I am trying to use your explanation for the quotes when specifying code, but it come up empty: wsPh.Range("f5:AD5").Formula = "=CWW SCHEDULE FOR COLLECTIONS FOR _ & "" "" & MONTH($AE$1) & "" "" & YEAR($AE$1)" Also, how do I format the MONTH as MMMMM instead of MM (i.e. CWW SCHEDULE FOR COLLECTIONS FOR MAY 2009 "Dave Peterson" wrote: Ps. I didn't test this. I had no idea what was in F5 and what CWWType and CWWDate were. Dave Peterson wrote: Does this formula do what you want if you enter it manually? =IF(F$5="","x",IF(ISNA(MATCH($F$5,CWWDate,0)),"", INDEX(CWWType,MATCH(F$5,CWWDate,0),1))) If yes, then try: wsPH.Range("f6:AD6").Formula _ = "=IF(F$5="""",""x"",IF(ISNA(MATCH($F$5,CWWDate,0)) ," _ & """"",INDEX(CWWType,MATCH(F$5,CWWDate,0),1)))" I move the "x" to the front and changed the comparison to equal. It's easier for my brain to see what's going on. Tony wrote: I have the following code that gives me a #N/A for some cells. I want to change it to "" using an error statement such as =IF(ISNA(.... Can anyone assist me here. wsPh.Range("f6:AD6").Formula = "=IF(f$5<"""",INDEX(CWWType, MATCH(f$5,CWWDate,0),1),""x"")" -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iserror help | Excel Discussion (Misc queries) | |||
use of ISERROR | Excel Worksheet Functions | |||
ISERROR - #NAME? | Excel Worksheet Functions | |||
ISERROR | Excel Worksheet Functions | |||
iserror | Excel Worksheet Functions |