VBA is *not* empty

ireland87

Board Regular
Joined
Jul 22, 2015
Messages
50
This code works for empty cells

How can I amend it to work when the cells in column C has a value of "No" for example?

Many thanks
Code:
Sub System_Check()
    Dim cell As Range, c As Range, cad As String
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    
    On Error Resume Next
    Set cell = Range("C4:C" & lr).SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not cell Is Nothing Then
        For Each c In cell
            cad = cad & c.Offset(, -2) & ", "
        Next
    End If
    If cad <> "" Then
        MsgBox "Systems Closed : " & Left(cad, Len(cad) - 2)
    Else
        MsgBox "all checked"
    End If
End Sub
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,703
Office Version
365
Platform
Windows
Amend to suit your own needs

Code:
Sub System_Check()
    Dim c As Range, cad As String, lr As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For Each c In Range("C4:C" & lr)
        [COLOR=#ff0000]If c = "No"[/COLOR] Then cad = cad & c.Offset(, -2) & ", "
    Next
    If cad <> "" Then
        MsgBox "Systems Closed : " & Left(cad, Len(cad) - 2)
    Else
        MsgBox "all checked"
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,096,306
Messages
5,449,572
Members
405,572
Latest member
Oberon

This Week's Hot Topics

Top