How do I get rid of #N/A

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 4 worksheets in a workbook...2 of the worksheets almost mirro each other. I am using VLOOKUP to populate fields in another worksheet...when nothing is enter...I get #N/A. I can get rid of this by using...example =IF(B4="N/A","",B4) within a worksheet but cannot from another worksheet within the workbook....HELP
 
You should use the designed arror functions. In this case ISNA. It is used
thus...

=IF(ISNA(VLOOKUP(Blah,Blah,Blah)),"",VLOOKUP(Blah,Blah,Blah))

ISNA only traps the #N/A
ISERR traps all, EXCEPT #N/A
ISERROR traps everything

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
And if you have a whole whack of these to change......

Sub NATrapAdd()
Dim myStr As String
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = True Then
If Not Cel.Formula Like "=IF(ISNA*" Then
myStr = Right(Cel.Formula, Len(Cel.Formula) - 1)
Cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")"
End If
End If
Next
End Sub

Gord Dibben XL2002
 
Gord,

I really like that!

Another very useful VBA routine to add to my collection.

Regards,
Kevin
 
Thanks, Kevin

Versatile also.

Just change the ISNA to ISERROR.

Or with a slight change.............

Sub RoundAdd()
Dim myStr As String
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = True Then
If Not Cel.Formula Like "=ROUND(*" Then
myStr = Right(Cel.Formula, Len(Cel.Formula) - 1)
Cel.Value = "=ROUND(" & myStr & "," & "0" & ")"
End If
End If
Next
End Sub

Gord
 
Back
Top