Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 313
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 515
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 313
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 515
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 313
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 515
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 313
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 313
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 313
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 313
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Iserror help Jim Excel Discussion (Misc queries) 3 December 11th 08 10:11 PM
use of ISERROR NathanG Excel Worksheet Functions 8 January 24th 07 07:31 PM
ISERROR - #NAME? Mpaul Excel Worksheet Functions 2 February 20th 06 11:27 PM
ISERROR Dee Excel Worksheet Functions 4 August 24th 05 07:37 PM
iserror Bill R Excel Worksheet Functions 1 August 4th 05 07:31 PM


All times are GMT +1. The time now is 04:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"