Clearing #N/A's in one go?

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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

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


On Tue, 22 Nov 2005 00:59:05 GMT, "Lee Harris" <[email protected]> wrote:

>
>"Tim C" <[email protected]> wrote in message
>news:[email protected]...
>> 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" <gorddibbATshawDOTca> wrote in message
news:[email protected]...
> 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?

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

On Tue, 22 Nov 2005 07:57:08 GMT, "Lee Harris" <[email protected]> wrote:

>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:[email protected]...
>> 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?
>