Find #N/A in column D

H

Howard

This finds blank cells, if any, in column D and makes note of such in a msgbox.
How can I do the same with #N/A's in the same column D. The #N/A's, if any, are a result of formulas. I tried altering the formula to produce "" instead of #N/A but still cannot sniff out those cells to include within the msgbox.

Option Explicit

Sub DcEmptyCells()
Dim i As Long
Dim j As Long
Dim ws As Worksheet
On Error Resume Next
i = Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row) _
.SpecialCells(xlCellTypeBlanks).Count
If i > 0 Then
MsgBox " You have " & i & " blank(s)." & vbCr & _
"In need of update column D:D.", vbOKOnly, "Blank & N/A"
End If
End Sub

Thanks.
Regards,
Howard
 
B

Ben McClave

Howard,

Try:

Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row) _
..SpecialCells(xlCellTypeFormulas, xlErrors).Count

Ben
 
H

Howard

Howard,



Try:



Range("D1:D" & Range("D" & Rows.Count).End(xlUp).Row) _

.SpecialCells(xlCellTypeFormulas, xlErrors).Count



Ben

Thanks,Ben. I had gone to help to see if I could get a list of the ".SpecialCells(xlCellType...., xl#N/A)". Had no luck. I'll try again.

Your suggestion will for sure do the trick.

Thanks again.

Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top