iserror to eliminate #N/A

[Tony]

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"")"

 

[Tony]

I get a 1004 error now. Is this a code format error?

 

[Kassie]

Did you enter this in one line? Which one of the two is giving this error?

--
HTH

Kassie

Replace xxx with hotmail

 

[Tony]

It was the second one entered on one line as:

wsPh.Range("f6:AD6").Formula =
"=IF(ISNA(F$5,"""",IF(f$5<>"""",INDEX(CWWType,MATCH(f$5,CWWDate,0),1),""x""))"

 

[Kassie]

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

 

[Dave Peterson]

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.

 

[Dave Peterson]

Ps. I didn't test this.

I had no idea what was in F5 and what CWWType and CWWDate were.

 

[Tony]

Dave, this did the trick. All I had to do was take out the $ sign just before
the "F" after the first MATCH.

 

[Tony]

Kassie, thanks for your help. Dave's helped top finish off what we were
working on. Much appreciated.

 

[Tony]

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)))"

Ps. I didn't test this.

I had no idea what was in F5 and what CWWType and CWWDate were.

 

[Dave Peterson]

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

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)))"

 

[Tony]

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

Ps. I didn't test this.

I had no idea what was in F5 and what CWWType and CWWDate were.

 

[Dave Peterson]

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""))"

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