iserror to eliminate #N/A


T

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

Advertisements

K

Kassie

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

--
HTH

Kassie

Replace xxx with hotmail
 
T

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

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
 
D

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

Advertisements

D

Dave Peterson

Ps. I didn't test this.

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

Tony

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

Tony

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

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


Dave Peterson said:
Ps. I didn't test this.

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

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

Advertisements

T

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

Dave Peterson said:
Ps. I didn't test this.

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

Advertisements

D

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
 

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

Excel VBA Vlookup 3
VBA Cell Formula 8
MATCH returning #N/A 3
IF(ISNA.. or ISERROR? question.. 3
Complex match 1
Correct #N/A Value 6
Showing Last number only 6
#N/A 3

Top