Code not finding criteria = "N/A"

[kc_native]

I am trying to execute a For Loop that goes through a range looking for cells that are blank, or contain an #N/A. The reason I have to do this is that I am wanting to do a CountA to find the number of cells that contain the data I want to count, but unless I do a ClearContents on the blank cells, the CountA counts them (and of course the #N/A cells as well). That one is a mystery to me, but here is the For Loop I am using to accomplish the ClearContents function:

For lngRow = 3 To Cells(Rows.Count, 1).End(xlUp).Row - 2
        If (Cells(lngRow, lngCol + 2) = "" Or Cells(lngRow, lngCol + 2) = "#N/A") Then
            Cells(lngRow, lngCol + 2).ClearContents
        End If
Next lngRow

The problem is that the For Loop is blowing up when it encounters the first #N/A, and giving me a Type Mismatch error. What am I doing wrong here?

 

[VoG]

Perhaps

For lngRow = 3 To Cells(Rows.Count, 1).End(xlUp).Row - 2
    If IsError(Cells(lngRow, lngCol + 2)) Then
        Cells(lngRow, lngCol + 2).ClearContents
    ElseIf Cells(lngRow, lngCol + 2) = "" Then
        Cells(lngRow, lngCol + 2).ClearContents
    End If
Next lngRow

 

[ChrisM]

you are treating "N/A" as a cell value. It's not really an explicit value, it's more of a "state" that the cell is in. use the ISERROR() function instead.

If (Cells(lngRow, lngCol + 2) = "" Or ISERROR(Cells(lngRow, lngCol + 2)) Then

Sometimes, can't remember if it's more Excel or VBA, you will get an error if you try to test an N/A cell for a specific value. In that case, swap the order of your tests:

If ISERROR(Cells(lngRow, lngCol + 2)) OR (Cells(lngRow, lngCol + 2) = "" Then

 

[tushiroda]

untested, though the below might work.

you should look at the COUNTIF and ISNA functions as a possible formula workaround as well.

For lngRow = 3 To Cells(Rows.Count, 1).End(xlUp).Row - 2
        If (Cells(lngRow, lngCol + 2) = "" Or application.worksheetfunction.isna(Cells(lngRow, lngCol + 2)) = true) Then
            Cells(lngRow, lngCol + 2).ClearContents
        End If
Next lngRow

 

[kc_native]

you are treating "N/A" as a cell value. It's not really an explicit value, it's more of a "state" that the cell is in. use the ISERROR() function instead.

If (Cells(lngRow, lngCol + 2) = "" Or ISERROR(Cells(lngRow, lngCol + 2)) Then

Sometimes, can't remember if it's more Excel or VBA, you will get an error if you try to test an N/A cell for a specific value. In that case, swap the order of your tests:

If ISERROR(Cells(lngRow, lngCol + 2)) OR (Cells(lngRow, lngCol + 2) = "" Then

Thanks, Chris. Your first line of code worked just great.

Now, can you tell me how a cell that appears blank in the cell location, and in the formula window, can be counted by a CountA function? I have never encountered this before.