Results 1 to 2 of 2

Thread: VBA is *not* empty
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA is *not* empty

    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

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA is *not* empty

    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)
            If c = "No" 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •