I have a problem including in my code the #N/A value in the cell. In my sheet, I want to eliminate the cell's content is the its value is either 0 or #N/A, but i don't know to write the VBA code for the #N/A.
This is the code that works for me, but it still gives an error when it encounters the #N/A cells.
For Each value In Sheets("Comments").Range("C2:Y600")
If value = 0 Or value = "#N/A" Then
value = " "
End If
Next
Instead of testing for the value test if the cell IsNA
Dim c As Range
Dim DefaultValue As String
DefaultValue = " "
For Each c In Sheets("Comments").Range("C2:Y600")
If Application.IsNA(c) Then
c.Value2 = DefaultValue
Else
If c.Value2 = 0 Then
c.Value2 = DefaultValue
End If
End If
Next
value as this is a reserved keyword. Also, how have you declared your variable?
if because if it contains NA then testing for c.Value2 = 0 fails. You need 2 If the first testing for Application.IsNA(c) and the second one testing for c.Value2 = 0 but that is all explained in the link above your question (which is a duplicate of your question and was already solved).