VBA is *not* empty

ireland87

Board Regular
Joined
Jul 22, 2015
Messages
52
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,359
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,102,576
Messages
5,487,661
Members
407,608
Latest member
kjw

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top