CODEWITHSUNDEEP
excelvba
sbagnato
sbagnato

Reputation: 496

Excel VBA code to replace string in range of cells

I am trying to add code that will search for the string #DIV/0! in a range of cells (EF:W10) and if found, will replace that cell with NA. Below is what I have, but it is not quite working. What am I missing?

Dim rngDiv As Range
For Each rngDiv In Range("E4:W10")
    If InStr(rngDiv.Value, "#DIV/0!") > 0 Then
        rngDiv.Value = "NA"
    End If
Next rngDiv

Upvotes: 0

Views: 1064

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

How about:

Sub marine()
    Dim rngDiv As Range
    For Each rngDiv In Range("E4:W10")
        If rngDiv.Text = "#DIV/0!" Then
            rngDiv.Value = "NA"
        End If
    Next rngDiv
End Sub

Upvotes: 0

SierraOscar
SierraOscar

Reputation: 17637

If you really need VBA to do this then you can use the IsError() function

Dim rngDiv As Range
For Each rngDiv In Range("E4:W10")
    If IsError(rngDiv) Then
        rngDiv.Value = "NA"
    End If
Next

However it's best to catch your errors in the actual formula rather than afterwards with VBA.

Note: The above will catch all errors - if you just want the #DIV/0! error then you need to test for the .Text property instead of the .Value property:

Dim rngDiv As Range
For Each rngDiv In Range("E4:W10")
    If rngDiv.Text = "#DIV/0!" Then
        rngDiv.Value = "NA"
    End If
Next rngDiv

Upvotes: 3

Related Questions