At least one cell in a range is not empty-CodePudding

I want to check if specific range (L32,M32;N32;O32;P32;Q32,R32;S32;T32).

If one of the cells is not empty a message should be displayed "FSFV check".

For Each cell In Range("L32:T32")
    If cell.Value <>  "" Then
        MsgBox "Check with CRA if FSFV was performed and notify RA"
    Else
    
    End If          
Next
    
End Sub

It displays the message eight times but I only want it once.

CodePudding user response：

If a Cell in a Range Is Blank...

If you're practicing loops, you could do the following.

Sub Test1()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

    Dim cell As Range

    For Each cell In ws.Range("L32:T32").Cells
        If Len(CStr(cell.Value)) = 0 Then  ' cell is blank
            MsgBox "Check with CRA if FSFV was performed and notify RA", _
                vbExclamation
            Exit For ' blank cell found, stop looping
            ' Or:
            'Exit Sub ' blank cell found, stop looping
        End If
    Next cell

    ' With 'Exit For' you'll end up here
    ' and you could continue with the sub.

End Sub

If not, rather use the following.

Sub Test2()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

    If Application.CountBlank(ws.Range("L32:T32")) > 0 Then
        MsgBox "Check with CRA if FSFV was performed and notify RA", _
            vbExclamation
    End If

End Sub

Hardly Related But Interesting

If you were wondering what happens to an object-type Control variable (in this case cell) in a For Each...Next loop when the loop has finished uninterrupted, the following example proves that it is set to Nothing.

Sub Test3()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

    Dim cell As Range
 
    For Each cell In ws.Range("L32:T32").Cells
        If Len(CStr(cell.Value)) = 0 Then Exit For
    Next cell
    
    If Not cell Is Nothing Then
        MsgBox "Check with CRA if FSFV was performed and notify RA", _
            vbExclamation
        Exit Sub
    End If
    
    MsgBox "Continuing...", vbInformation

End Sub

CodePudding user response：

How about :

Sub Test()

Dim AnyData As Integer

AnyData = WorksheetFunction.CountA(Range("L32:T32"))

If AnyData = 0 Then
    Exit Sub
    Else
    MsgBox "Check with CRA if FSFV was performed and notify RA"
End If

End Sub

CodePudding user response：

Let me give you the simplest approach:

Dim Found As Boolean
Found = False
For Each cell In Range("L32:T32")
    If cell.Value <> "" Then
        Found = True
    End If
Next
If Found Then
    MsgBox "Check with CRA if FSFV was performed and notify RA"
End If

As you see, the fact that you have found an empty cell is kept in a Boolean variable, and afterwards you just use that information for showing your messagebox.