Run-time error '13'

Question

Run-time error '13'

Navigation

#1 by (1 votes)

1

I am creating a macro to delete the cell values that contains # N / A, but it gives me the presents (Runtime error '13')

When I put # N / A the code works.

Can anyone help me solve the problem?

 Private Sub CommandButton1_Click()
'percorre da linha 2 até a última preenchida em A
   For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    'se em B for #N/D
    If Cells(i, 2) = "#N/D" Then
    'apaga conteúdo de A
    Cells(i, 2).ClearContents
    End If
  'próxima linha
 Next i

End Sub

vba

asked by anonymous 14.05.2018 / 19:22

1 answer

How to display data from three tables in a view? How to access elements of a vector of a type defined by struct?

score 1 · Accepted Answer

You can use the isNA ()

Code

'percorre da linha 2 até a última preenchida em A
   For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    'se em B for #N/D
    If WorksheetFunction.IsNA(Cells(i, 2)) Then
    'apaga conteúdo de A
    Cells(i, 1).ClearContents
    End If
  'próxima linha
 Next i

Error

This error occurs because # N / A is not a String, but an error variable. So the isNA() function is used. However, # N / A can be searched with simple String search functions, such as .Find

Another way

Is performed using the CVerr ()

'percorre da linha 2 até a última preenchida em A
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    'se em B houver erro
    If IsError(Cells(i, 2)) Then
        'Se em B for #N/D
        If Cells(i, 2) = CVErr(xlErrNA) Then
            'apaga conteúdo de A
            Cells(i, 1).ClearContents
        End If
    End If
    'próxima linha
Next i