Clearing #N/A's in one go?

Clearing #N/A's in one go?

Post by Lee Harri » Wed, 23 Nov 2005 09:35:54


Is there a way, after the fact of selecting a large range, and automatically
clearing the contents of any cell with #N/A as a result of unmatched VLOOKUP
or similar.

tks
 
 
 

Clearing #N/A's in one go?

Post by Tim » Wed, 23 Nov 2005 09:48:10

F5 > Special > Formulas > [uncheck everything except] Errors> Ok > Delete

But we can also help you modify your formulas so that the errors don't
appear in the first place.

Tim C

 
 
 

Clearing #N/A's in one go?

Post by Lee Harri » Wed, 23 Nov 2005 09:59:05


thanks, I think I am OK using the ISNA wrapper in my formulas, but when
you've typed out a long, complex formula sometimes I can't be bothered to
put it in - your tip will be a great time saver thanks!
 
 
 

Clearing #N/A's in one go?

Post by Gord Dibbe » Wed, 23 Nov 2005 10:30:41

Lee

To add the ISNA wrapper to all VLOOKUP formulas after the fact....

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 Excel MVP
 
 
 

Clearing #N/A's in one go?

Post by Lee Harri » Wed, 23 Nov 2005 16:57:08


wow, thanks Gordon!

is that just added to any particular worksheet code with Alt-F11 or?
 
 
 

Clearing #N/A's in one go?

Post by Gord Dibbe » Thu, 24 Nov 2005 03:52:03

Lee

ALT + F11 to open VB Editor

Select your workbook/project and right-click and insert module.

Place the code in that general module.

Save workbook.

ALT + Q to return to Excel workbook.

Macro can be run by ALT + F8 to open Macros or assigned to a button or
shortcut key combo.

If you want it to be available for all workbooks, place the Sub in your
Personal.xls.


Gord Dibben Excel MVP