Search Range for NAs and pop up with a message box

**morbdetro** · 06-07-2013, 02:14 PM

I am having trouble with a portion of my code.

currently I am using this code I found

Dim cell As Excel.Range
For Each cell In Selection
If WorksheetFunction.ISNA(cell) = True Then MsgBox ("#NA")
Next cell

Application.ScreenUpdating = True

To search through a selected range with forumulas and if there is NA value, put up a message box with "#NA". It works but is not exactly what I am looking for. The code will put up a message box for each NA it finds (I know that is what it is suppose to do).

. I am looking for a code that will evaluate the selected range and if it finds 1 or more "NA" then pop up 1 message box and then end sub. I have combed the internet but this code is the closest I can find to what I am looking for. I am trying to avoid writting a code that will evaluate 1 cell at a time as my range can be as large as 10,000 cells Any help would greatly be appreciated.

**Jakobshavn** · 06-07-2013, 02:29 PM

Consider:

Sub NATest()
    Dim cell As Range
    For Each cell In Selection
        If WorksheetFunction.IsNA(cell) = True Then
            MsgBox ("#NA")
            Exit For
        End If
    Next cell
End Sub