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

 

[shg]

Sub IsEmptyRange()
  Dim cell          As Range

  On Error Resume Next
  Set cell = Range("A1:B5").SpecialCells(xlCellTypeBlanks)(1)

  If Err.Number Then
    MsgBox "No blanks"
  Else
    cell.Select
    MsgBox "That's empty"
  End If
  
  On Error GoTo 0
End Sub

 

[ireland87]

Hi shg

unfortunately that is looking at the incorrect range and doesn't return the desired result.

It needs to look at Column C ensure there's no blanks then return a message box containing the system in column A

 

[Steve_]

Sub IsEmptyRange()
Dim cell As Range
Dim bIsEmpty As Boolean
bIsEmpty = False
For Each cell In Range("C1:C5")
If IsEmpty(cell) = True Then
'An empty cell was found. Exit loop
bIsEmpty = True
Exit For
End If
Next cell
If bIsEmpty = True Then
'There are empty cells in your range
'**PLACE CODE HERE**
MsgBox "System: " & Range(Replace(cell.Address, "C", "A")) & " is blank."
Else
'There are NO empty cells in your range
'**PLACE CODE HERE**
MsgBox "All cells have values!"
End If
End Sub

 

[DanteAmor]

Try this:

Sub EmptyRange()
    Dim r As Range, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set r = Range("C1:C" & lr + 1)
    n = Range("C" & Evaluate("=MIN(IF(" & r.Address & "="""",ROW(" & r.Address & ")))")).Row
    If n > lr Then
        MsgBox "There are NO empty cells in your range"
    Else
        MsgBox "System hasn't opened : System " & Range("A" & n)
    End If
End Sub

 

[shg]

Sub IsEmptyRange()
  Dim cell          As Range

  On Error Resume Next
  Set cell = Range("C2:C6").SpecialCells(xlCellTypeBlanks)(1)

  If Err.Number Then
    MsgBox "No blanks"
  Else
    MsgBox Cells(cell.Row, "A")
  End If
  
  On Error GoTo 0
End Sub

 

[ireland87]

Try this:

Sub EmptyRange()
    Dim r As Range, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set r = Range("C1:C" & lr + 1)
    n = Range("C" & Evaluate("=MIN(IF(" & r.Address & "="""",ROW(" & r.Address & ")))")).Row
    If n > lr Then
        MsgBox "There are NO empty cells in your range"
    Else
        MsgBox "System hasn't opened : System " & Range("A" & n)
    End If
End Sub

Can it list all the system that opened? rather than just one?

is there any way to be more specific regarding the timing? for example if I was writing an IF statement it might go something like;

IF(INDEX(Open,MATCH(A2,System,0))<TIME(8,15,0),"Yes","no")

 

[ireland87]

Foe time specific

<time(8,15,0)
IF(INDEX(Open,MATCH(A2,System,0)) greatertthan TIME(8,15,0) <time(8,15,0)
then look through the data in column C and return any that are blank</time(8,15,0)
</time(8,15,0)

 

[DanteAmor]

what do you need? the full cells or the empty cells, you can make your example bigger, show the expected result, where do you want the result in a msgbox, in a cell, do you want the macro or a formula?

 

[ireland87]

ideally what i need is at each possible time 7am / 8am and 8:30am check column C is still if any blank, if it is display a message box showing all of the systems that have not been checked.

something

If Cells(i, 2).Value >= TimeValue(Now) And Cells(i, 3).Value <> ""  Then

loop through all the possibilities and kick out a msgbox containing each system not was not checked.

then repeat

or would this work better

Cells(i, 2).Value = TimeSerial(7, 0, 0) And Cells(i, 3).Value <> ""  Then

Cells(i, 2).Value = TimeSerial(8, 0, 0) And Cells(i, 3).Value <> ""  Then