I want to check text in a range of cells, Columns A to L and Rows 2 to 10, I want to use a for to go through that range, but I get an error

For x = 1 To 12 Step 1
 For i = 2 To 10 Step 1
    If Range(x, i).Value = "#REF!" Then
     Range(x, i) = "0"
    End If
   Next
 Next

CodePudding user response：

Multiple issues:

• Use Cells instead of Range.
• Test if the cell contains a #REF! error

For x = 1 To 12 Step 1
    For i = 2 To 10 Step 1
        If IsError(Cells(x, i).Value) Then
            If Cells(x, i).Value = CVErr(xlErrRef) Then
                Cells(x, i).Value = 0 '<--- no quotes, write a number not a string
            End If
        End If
    Next
Next

CodePudding user response：

No need to use numerical loop, you can use structure like this, if you want to detect all #REF errors and replace them with zero.

Sub test()
    Dim itm As Variant
    For Each itm In Range("A2:L10")
        If IsError(itm.Value) Then
            If itm.Value = CVErr(xlErrName) Then itm.Value = 0
        End If
    Next itm
End Sub