Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing #N/A's in one go?
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing #N/A's in one go?
"Tim C" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing #N/A's in one go?
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" wrote: "Tim C" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing #N/A's in one go?
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Clearing #N/A's in one go?
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" wrote: "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line Graphs - How do I plot N/As? | Charts and Charting in Excel | |||
Clearing Check Boxes and Cells | Excel Discussion (Misc queries) | |||
Clearing Check Boxes and Cells | Excel Discussion (Misc queries) | |||
Clearing Check Boxes and Cells | Excel Worksheet Functions | |||
Pivot Table to Ignore N/As | Excel Discussion (Misc queries) |