Clearing #N/A's in one go?

[Lee Harris]

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

 

[Tim C]

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

 

[Lee Harris]

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

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!

 

[Gord Dibben]

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

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

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!

 

[Lee Harris]

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

wow, thanks Gordon!

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

 

[Gord Dibben]

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