Excel VBA code to replace string in range of cells

Question

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

Instead of using a sledgehammer to kill a fly, why don't you change the formula in those cells to =IFERROR([current formula],"NA") ? — SierraOscar
– SierraOscar, Commented Jan 4, 2017 at 15:13
No can do. The entire code needs to be in the script so that it can be run on a blank new excel sheet. — sbagnato
– sbagnato, Commented Jan 4, 2017 at 15:18
But then the script must be adding the formula to your cells at some point. Make the script add macromans formula instead of the current one. — tomdemaine
– tomdemaine, Commented Jan 4, 2017 at 15:24

SierraOscar · Accepted Answer · 2017-01-04 15:24:41Z

3

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

answered Jan 4, 2017 at 15:24

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sbagnato Over a year ago

Yes this is what I was looking for. Thank you

Gary's Student · Accepted Answer · 2017-01-04 15:24:42Z

0

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

answered Jan 4, 2017 at 15:24

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

1 Comment

SierraOscar Over a year ago

Strangely enough - I was actually going to link to an old post of yours...

Collectives™ on Stack Overflow

Excel VBA code to replace string in range of cells

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related