Can anyone correct my 3liner of codes?

[edlim85]

im getting a error here..pls help

As the code, can tell what i want to do is go through each cells in my used range. clearcontent for cells containing "NA" or "#N/A"

For Each cell In w1.UsedRange.cells
If cell.Value = "NA" Or cell.Value = "#N/A" Then cell.ClearContents
Next cell

Regards
Edmund

 

[RichardMGreen]

What have you defined cell as?

This bit
w1.UsedRange.cells
should just be
w1.UsedRange

 

[edlim85]

Thanks RichardMGreen for ur prompt reply.

dim cell as range, i step into my macro the cells until #N/A which is error 2042. how do i clear this?

 

[edlim85]

I have NA (which is my input) and #N/A that i want to clear.

i just found this. maybe i should write separate code to clear NA and #N/A

For Each cell In Range("MSLnew").SpecialCells(xlCellypeFormulas,xlErrors)
If cell.Text = "#N/A" Then cell.ClearContents
Next cell

 

[RichardMGreen]

Change this line:-
If cell.Value = "NA" Or cell.Value = "#N/A" Then cell.ClearContents
to
If cell.Value = "NA" Or iserror(cell.Value) Then cell.ClearContents

You can't test for an error in the way you're trying to. This way you'll get rid of all errors, not just the #N/A ones.

 

[Peter_SSs]

What have you defined cell as?

This bit
w1.UsedRange.cells
should just be
w1.UsedRange

Actually, including the .Cells is probably better since it specifically states what property of the range to use, rather than relying on the default as many of us often do. It certainly isn't wrong to include it.

For example, take a fresh worksheet and fill a small (say 3 x 3) range somewhere on the sheet with data.

Put the below code in a Module and step through a line at a time with F8 and observe the number of loops and the sheet results.

Then clear the cell colour and change the .Rows to .Cells and repeat the step-through.

Sub test()
    Dim cell As Range
    
    For Each cell In ActiveSheet.UsedRange.Rows
        cell.Interior.ColorIndex = 3
    Next cell
End Sub

 

[mikerickson]

If cell contains an error, a typemismatch error results from trying to compare cell.Value = "NA". This protects against that

If CStr(cell.Value) = "NA" Or IsError(cell.Value) Then cell.ClearContents

This will test for the string "NA" and the error value "#N/A"

If CStr(cell.Value) = "NA" Or CStr(cell.Value) = CStr(CVErr(xlErrNA)) Then cell.ClearContents

 

[edlim85]

Thanks to all for your replies. I'm still new and learning here.

If CStr(cell.Value) = "NA" Or CStr(cell.Value) = CStr(CVErr(xlErrNA)) Then cell.ClearContents

this works.

thanks again


Regards
Edmund

 

[RichardMGreen]

Peter - I must admit I've never used .Cells when I've referenced UsedRange, but I'm happy to be corrected.

 

[Peter_SSs]

Edmund

Stepping through every cell in the used range seems like it could be more work than is needed. How big is your used range?

Do you have other error values besides #N/A that you want to keep, or could youn use something like this to get them all in a couple of swipes?

With Sheets("MSLnew").UsedRange
    On Error Resume Next
    .SpecialCells(xlCellTypeFormulas, xlErrors).ClearContents
    On Error GoTo 0
    .Replace What:="NA", Replacement:="", LookAt:=xlWhole, _
        Searchformat:=False, ReplaceFormat:=False
End With