VBA to detect empty cells in range formatted as time values...not?

[OaklandJim]

I thought I'd gotten a better understanding of times in VBA and worksheets. Not quite. In VBA trying to test whether a cell is empty. Easy huh? IsEmpty(.Value) or if .value = "" right?

I'm missing SOMETHING. I cannot tell whether a cell that MIGHT be a time is empty. Empty cells keep evaluating to zero which is a time in Excel: Midnight. So my loop looking for specific times in an input range keeps detecting lots of phantom, 12:00 AM values. Of course I have one midnight value to detect so <> 0 does not work. Help me find the light of day here. Thanx In advance.

 

[DataBlake]

you can probably use an if statement that says if isempty(cell.value2)
can you post your VBA code using the code tags & what the value of the cells you're trying to detect/not detect are in your formula bar?

edit: here is info on value2

Range.Value2 property (Excel)

Office VBA reference topic

docs.microsoft.com

 

[Jeffrey Mahoney]

How about this:

Sub FindNonTimeCell()
  Dim Cel As Range
  For Each Cel In Selection
    If IsNumeric(Cel) = False Or Cel.Value = 0 Then
      Debug.Print Cel.Address & " Not Time"
    Else
      Debug.Print Cel.Address & " Time"
    End If
  Next Cel
 
End Sub

 

[OaklandJim]

You know, often help boards help just by forcing one to think more about an issue. I started by testing cell.value <> "". That did not work. So I tried IsEmpty. Dumbass me, looking at my code too much blinded me to the fact that I am using IsEmpty function to test IsEmpty(cell.value). That evaluates to true.

Jeffrey Thanx. Maybe I'm missing something about your nice piece of code but zero is midnight in Excel's time zone. EMPTY cell is not a time, in my case.

Thanks again team MrExcellent.

 

[DataBlake]

Glad you found a solution, cheers.