at least one of of a cell is not empty in a range-CodePudding

I hope you can help: I want to check if in a specific range (L32,M32;N32;O32;P32;Q32,R32;S32;T32) one or more of the cells are not empty. If one of the cell is not empty a message should be displayed " FSFV check". How can I do this?

Many thanks Nicole

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 now the message 8 times but I only want it once. That means it should check if one of the cell is not empty then display the message once.

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：

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