CODEWITHSUNDEEP
excelvba
Savagefool
Savagefool

Reputation: 223

Excel-VBA : search for cell with specific time value

This is my code:

Sub FindMatchingValue()
    Dim i As Integer, TimeValueToFind As Date
    TimeValueToFind = "00:00:00"
    Sheets("Vessels").Range("F07").ClearContents
    For i = 1 To 25   '
        If Sheets("Vessels").Cells(i, 1).Value = TimeValueToFind Then
            MsgBox ("Found value on row " & i)

            Sheets("Vessels").Range("F07").Value = Cells(i, 1).Offset(1, 1).Resize(1).Value
            Exit Sub
        End If
    Next i
    MsgBox ("Value not found in the range!")
End Sub

This code checks Column A for the time inputted in the format xx:xx:xx Both where the input is, and where the times are written are set as "Time" format

There is a table with times in from 00:00:00 to 23:00:00 in Column A. For some reason, this code will only work when trying to find 00:00:00, no other time value is found at all. I can't figure out the reason why.
I thought it might be because TimeValueToFind is "as Date". However there seems to be no "As Time" though? Unsure why this is not working. And if that's the case why it doesn't work, why is 00:00:00 found but nothing else? It's a simple error I bet but some help would be greatly appreciated.

For additional information:

Tidal Time is Column A.

Tidal Time  Tidal Height
00:00:00    
01:00:00    
02:00:00    
03:00:00    
04:00:00    4.40
05:00:00    4.00
06:00:00    3.60
07:00:00    3.20
08:00:00    2.80
09:00:00    2.40
10:00:00    2.00
11:00:00    2.37
12:00:00    2.74
13:00:00    3.11
14:00:00    3.49
15:00:00    3.86
16:00:00    4.23
17:00:00    4.60
18:00:00    4.13
19:00:00    3.67
20:00:00    3.20
21:00:00    2.73
22:00:00    2.27
23:00:00    1.80

The Entirity of Column A is Time. Now It's important to understand that for awhile now I've been punching myself in the face as for i to 25 should have been 2 to 25, the String Tidal Height is incorrect format and this was the reason for the missmatch error.

Upvotes: 2

Views: 3544

Answers (3)

ssarabando
ssarabando

Reputation: 3517

That's because you're trying to compare apples and oranges. TimeValueToFind is of type Date and Sheets("Vessels").Cells(i, 1).Value yields a double (add both to the watches window and check).

You can make it work either by converting TimeValueToFind to a double or casting Sheets("Vessels").Cells(i, 1).Value to a Date:

' (...)
If CDate(Sheets("Vessels").Cells(i, 1).Value) = TimeValueToFind Then
' (...)

Or:

' (...)
delta = Sheets("Vessels").Cells(i, 1).Value - CDbl(TimeValueToFind)
If Abs(delta) <= tolerance Then
' (...)

Upvotes: 1

Floris
Floris

Reputation: 46395

Internally, Excel stores numbers as a double. It is well known that the expression a==b is likely to return False when a and b are both doubles, even though you might think they are the same. This is due to the finite precision with which floating point numbers are stored.

You have various options.

1) Set a tolerance. If abs(a-b)<tolerance Then

2) Convert to a string with a fixed number of digits, and compare the strings. This is doing explicitly what the above does implicitly

The first approach is usually considered the way to go. You should ban testing for equality from your vocabulary when you are using floating point numbers.

Upvotes: 1

Matt
Matt

Reputation: 2802

Excel stores times as fractions of a day. So hour 00:00:00 is 0/24ths of a day. Hour 01:00:00 is 1/24th of a day or .041666, etc. You need to compare against those values. If you test the value in column A you will see it is a number from 0 to 1. If you run something like this you can see what's happening.

Sub Test()
Dim i As Integer
Dim Test As Variant

For i = 1 To 24
    Test = Sheets("Sheet1").Cells(i, 1).Value
    Sheets("Sheet1").Cells(i, 2).Value = Test
Next i

End Sub

Upvotes: 1

Related Questions