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

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,647
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,095,724
Messages
5,446,144
Members
405,384
Latest member
geowbadyt

This Week's Hot Topics

Top