PDA

View Full Version : Solved: search for #n/a



ElvisFan
02-21-2007, 08:24 AM
Hello, I am trying to do a search for #n/a in VBA. I want to clear the contents of all the cells that have #n/a in them. I keep getting a type mismatch.
Sub ClearNA()
For Each c In ActiveSheet.Cells
If c = xlErrNA Then
c.ClearContents
End If
Next c
End Sub
At some point it stops and I get an error 2042 type mismatch.
Can anyone help me on this?
Thanks

Dave
02-21-2007, 08:59 AM
Give this a trial. HTH. Dave

Sub ClearNA()
For Each c In ActiveSheet.UsedRange
If Application.WorksheetFunction.IsNA(c) Then
c.ClearContents
End If
Next c
End Sub

lucas
02-21-2007, 09:02 AM
Why not fix whatever is causing the error?

ElvisFan
02-21-2007, 10:15 AM
Dave, thanks that worked.
Lucas, Actually I want to see the error. I am running them up against a master list of items. So I want to know which items are on the master list but not on the smaller lists. The easiest way to do that is to just let the error show. Then when everything is done I clear out the errors. The macro is faster than filtering all the various lists to clear those fields.
Thanks

mdmackillop
02-21-2007, 02:25 PM
Try
Cells.SpecialCells(xlCellTypeFormulas, 16).ClearContents

ElvisFan
02-22-2007, 03:23 PM
Thank you.