IsEmpty VBA

[ireland87]

hello

I'm looking at a scenario such as the below;

I've defined names;
A2:A6 = System
B2:B6 = OpenTime
C2:C6 = Checked

The current time is 08:15

I would like to perform an "EmptyCheck" based on the time (08:00) but ignoring empty cells whereby the check would be completed after 08:00 i.e. the one at 08:30.
Then return a message box stating which system hasn't opened. For this example I would like it to return "system B"

[TABLE="width: 500"]
<tbody>[TR]
[TD]System[/TD]
[TD]Time[/TD]
[TD]Checked[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]08:00[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]08:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]07:00[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]08:00[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]08:30[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This code wellsr.com almost does what I need but I can't adapt it correctly.

[COLOR=inherit][FONT=Monaco][B]Sub[/B][/FONT][/COLOR][COLOR=inherit][FONT=Monaco] [/FONT][/COLOR][COLOR=#990000][FONT=Monaco][B]IsEmptyRange[/B][/FONT][/COLOR][COLOR=inherit][FONT=Monaco]()[/FONT][/COLOR]<code class="language-vb" data-lang="vb" style="box-sizing: border-box; font-size: inherit; border: 0px; border-radius: 0px; background-color: transparent; padding: 0px; font-family: Monaco, Menlo, Consolas, "Courier New", monospace; color: inherit; white-space: pre;">[B]Dim[/B] [COLOR=#008080]cell[/COLOR] [B]As[/B] Range
[B]Dim[/B] [COLOR=#008080]bIsEmpty[/COLOR] [B]As[/B] [COLOR=#445588][B]Boolean[/B][/COLOR]

bIsEmpty [B]=[/B] [B]False[/B]
[B]For[/B] [B]Each[/B] cell [B]In[/B] Range([COLOR=#DD1144]"A1:B5"[/COLOR])
    [B]If[/B] IsEmpty(cell) [B]=[/B] [B]True[/B] [B]Then[/B]
        [COLOR=#999988][I]'An empty cell was found. Exit loop[/I][/COLOR]
        bIsEmpty [B]=[/B] [B]True[/B]
        [B]Exit[/B] [B]For[/B]
    [B]End[/B] [B]If[/B]
[B]Next[/B] cell

[B]If[/B] bIsEmpty [B]=[/B] [B]True[/B] [B]Then[/B]
    [COLOR=#999988][I]'There are empty cells in your range[/I][/COLOR]
    [COLOR=#999988][I]'**PLACE CODE HERE**[/I][/COLOR]
    MsgBox [COLOR=#DD1144]"There are empty cells in your range"[/COLOR]
[B]Else[/B]
    [COLOR=#999988][I]'There are NO empty cells in your range[/I][/COLOR]
    [COLOR=#999988][I]'**PLACE CODE HERE**[/I][/COLOR]
    MsgBox [COLOR=#DD1144]"All cells have values!"[/COLOR]
[B]End[/B] [B]If[/B] </code>[COLOR=inherit][FONT=Monaco][B]End[/B][/FONT][/COLOR][COLOR=inherit][FONT=Monaco] [/FONT][/COLOR][COLOR=inherit][FONT=Monaco][B]Sub[/B][/FONT][/COLOR]

Anyone able to help me out?

Then I can have this;

Sub mytimer()Application.OnTime TimeValue("08:15:00"), "EmptyCheck"
End Sub

Which will then go into this;

Private Sub Workbook_Open()mytimer
End Sub

 

[DanteAmor]

Try this

Sub System_Check()
    Dim cell As Range, c As Range, cad As String
    
    On Error Resume Next
    Set cell = Range("C2:C6").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

 

[ireland87]

Many thanks Dante - that works a treat.

Try this

Sub System_Check()
    Dim cell As Range, c As Range, cad As String
    
    On Error Resume Next
    Set cell = Range("C2:C6").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

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[ireland87]

is there any way I can paste the result into a single cell on a worksheet?
rather than the result appearing in the msgbox

[COLOR=#333333][I]    If cad <> "" Then
[/I][/COLOR][COLOR=#333333][I]        MsgBox "Systems Closed : " & Left(cad, Len(cad) - 2)[/I][/COLOR]

 

[ireland87]

solved with

Worksheets("report").Range("E1") = "Systems Closed : " & Left(cad, Len(cad) - 2)

 

[DanteAmor]

I'm glad you solved it