Delete contents from cell, IFIsNumeric = False

Question

So I have this program; that scans values from a remote system. At times it can timeout if network is down and it will put in #nan error or something similar.

When I try and write that error message to a database; it errors out for a "Data Type Mismatch" since it is looking for a numerical value.

To get around this I decided to try and clean up the results. So I have that code below. It works most of the time; what I am seeing it fail at is if there is a numerical number halfway through and then it errors again. Once it gets to the numerical number it seems like it stops the loop and does not clean anything else.

Any help someone can give me would be great; and maybe I am doing this a bad way. I tried to give examples of what I am trying to see below. Please let me know if you have any suggestions or questions.

Private Sub Clean()
'Select Current Row For where to start cleaning. LoopIndex is a global variable. 
'Just giving the line of where to read from so I do not need to read the whole sheet. 
  Range("B" & LoopIndex).Select

'Start the loop, to go through the entire row.
  Do Until IsEmpty(ActiveCell)

   'Checks if it is a number value; if it is not it will clear out the data.
    If IsNumeric(ActiveCell.Value) = False Then
        ActiveCell.Value = ""
    End If
  ActiveCell.Offset(0, 1).Select
 Loop
End Sub

What it looks like.

|A     |B     |C     |D     |
|#error|#Error|3     |#Error|

What I want it to look like.

|A     |B     |C     |D     |
|      |      |3     |      |

What it is doing.

|A     |B     |C     |D     |
|      |      |3     |#Error|

Answer 1

Try this

Sub RemoveErrors(TheRange As Range)
Dim c
For Each c In TheRange
    If IsError(c) Then c.Value = ""
Next c
End Sub

Edit: Version without loop that may speed things up a bit if they are formula errors

Sub RemoveErrors(TheRange As Range)
On Error Resume Next
TheRange.SpecialCells(xlCellTypeFormulas, xlErrors).Value = vbNullString
On Error GoTo 0
End Sub

Use it like this:

Sub Test()
RemoveErrors Range("A1:D21")
End Sub

If you prefer to use the IsNumeric function, then use this:

Sub RemoveNAN(TheRange As Range)
Dim c
For Each c In TheRange
    If IsNumeric(c) = False Then c.Value = ""
Next c
End Sub