Finding and replacing #N/A with VBA

I’m trying to convert the #N/A errors created by an Xlookup.

The problem is that it gives me type mismatch error when a cell contains #N/A.

This is the code that gives me the error on the If line:

For i = 2 To lastRow ' Assuming data starts from row 2, adjust if needed
        If ws.Cells(i, 3).value = "DS De-scoped" Or IsError(ws.Cells(i, 3).value) Then
            ' Put '-- in all cells from D to L
            ws.Range(ws.Cells(i, 4), ws.Cells(i, 12)).value = "--"
            
            ' In D and M, put "DS"
            ws.Cells(i, 4).value = "DS"
            ws.Cells(i, 13).value = "DS"
        End If
Next i

>Solution :

Perhaps like so:

Sub demo()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim hit As Boolean
    Dim i As Long
    lastRow = 5
    Set ws = ActiveSheet
    For i = 2 To lastRow    ' Assuming data starts from row 2, adjust if needed
        hit = False
        If IsError(ws.Cells(i, 3).Value) Then
            hit = True
        ElseIf ws.Cells(i, 3).Value = "DS De-scoped" Then
            hit = True
        End If
        If hit Then
            ' Put '-- in all cells from D to L
            ws.Range(ws.Cells(i, 4), ws.Cells(i, 12)).Value = "--"

            ' In D and M, put "DS"
            ws.Cells(i, 4).Value = "DS"
            ws.Cells(i, 13).Value = "DS"
        End If
    Next i
End Sub