VBA Time comparison not correct

  • Thread starter Thread starter wright
  • Start date Start date
W

wright

I have a worksheet with a column of time values (column A). I entered the
first 3 manually

00:00
00:01
00:02
and then autofilled down to 23:59


I have another cell(c3) formatted the same as column A which I placed
another time value into 19:00.

In VBA I am comparing the time in C3 and with each of the values in column A
and if the times are the same I am placing a 1 in column B next to the same
time.

For n = 1 To 1440
If Cells(n, 1) = Cells(3, 3) Then
Cells(n, 2) = 1
End If
Next n

This is an oversimplification of a much more complex project but it
demonstrates the error exactly. The problem is that there are certain values
that do not seem to match even though they should. For example when I run
this with the value of 19:00 in C3 column b has no values even though B1141
should = 1.
 
Another note to mention, is that by entering the value manually (i.e. typing
19:00 into cell B1141) and rerunning the vba script it seems to correct the
error. So this is probably something funky to do with autofill.
 
It's likely rounding at the 14th decimal place or something like that.
Maybe you should compare the .Text properties rather than the .Value
properties.

--
Jim
| Another note to mention, is that by entering the value manually (i.e.
typing
| 19:00 into cell B1141) and rerunning the vba script it seems to correct
the
| error. So this is probably something funky to do with autofill.
 
Another note to mention, is that by entering the value manually (i.e. typing
19:00 into cell B1141) and rerunning the vba script it seems to correct the
error. So this is probably something funky to do with autofill.
Click to expand...

Search for the text instead of the value

Sub mmm()
For n = 1 To 1440
If Cells(n, 1).Text = Cells(3, 3).Text Then
Cells(n, 2) = 1
End If
Next n
End Sub

But, there is no need to go through all the looping. Try something
like this
Sub anotherWay()
Range("A1:A1440").Find(What:="19:00", After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1) = 1
End Sub
 
Search for the text instead of the value

Sub mmm()
    For n = 1 To 1440
       If Cells(n, 1).Text = Cells(3, 3).Text Then
            Cells(n, 2) = 1
       End If
    Next n
End Sub

But, there is no need to go through all the looping.  Try something
like this
Sub anotherWay()
    Range("A1:A1440").Find(What:="19:00", After:=ActiveCell, _
        LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Offset(0, 1) = 1
End Sub
Click to expand...

Oops. Didn't make that dynamic. Try this:

Sub anotherWay()
Range("A1:A1440").Find(What:=Cells(3, 3).Text, _
After:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, 1) = 1
End Sub
 
The loop is checking quite a few other conditions as well as setting values
in multiple places...I just simplified the vba to narrow the problem, but
thank you.
 
This worked for my example but unfortunately creates a new issue with my
actual code.

I am actually (and one of the reasons for the loop) entering 2 times and
want to put a 1 next to all times >= the first time and <= the last time.

Dim n As Integer
For n = 1 To 1440
'check if column 1 value is later than H1
If Cells(n, 1) >= Cells(1, 8) Then
'check if column 1 value is before I1
If Cells(n, 1) <= Cells(1, 9) Then
Cells(n, 2) = 1
End If
End If
Next n

Now with comparing the text values:

Dim n As Integer
For n = 1 To 1440
'check if column 1 value is later than H1
If Cells(n, 1).Text >= Cells(1, 8).Text Then
'check if column 1 value is before I1
If Cells(n, 1).Text <= Cells(1, 9).Text Then

Cells(n, 2) = 1
End If
End If
Next n

and using h1 = 15:00 and I1=23:59

I get 1:00 to 1:59 and 15:00 to 23:59 marked.

(note: I am also doing other calculations in this formula which require the
double if statements and the Cells(r,c) formats)
 
Well Text isn't so great for greater then/ less than comparisons.

Back to Value, I wonder if rounding will work:

Round(Cells(n, 1).Value,10) >= Round(Cells(1,8).Value,10)

"10" is arbitrary of course.

--
Jim
| This worked for my example but unfortunately creates a new issue with my
| actual code.
|
| I am actually (and one of the reasons for the loop) entering 2 times and
| want to put a 1 next to all times >= the first time and <= the last time.
|
| Dim n As Integer
| For n = 1 To 1440
| 'check if column 1 value is later than H1
| If Cells(n, 1) >= Cells(1, 8) Then
| 'check if column 1 value is before I1
| If Cells(n, 1) <= Cells(1, 9) Then
| Cells(n, 2) = 1
| End If
| End If
| Next n
|
| Now with comparing the text values:
|
| Dim n As Integer
| For n = 1 To 1440
| 'check if column 1 value is later than H1
| If Cells(n, 1).Text >= Cells(1, 8).Text Then
| 'check if column 1 value is before I1
| If Cells(n, 1).Text <= Cells(1, 9).Text Then
|
| Cells(n, 2) = 1
| End If
| End If
| Next n
|
| and using h1 = 15:00 and I1=23:59
|
| I get 1:00 to 1:59 and 15:00 to 23:59 marked.
|
| (note: I am also doing other calculations in this formula which require
the
| double if statements and the Cells(r,c) formats)
 
This is terrible programming but it works.

Dim n As Integer
For n = 1 To 1440
'check if column 1 value is later than H1
If Round(Cells(n, 1).Value, 10) >= Cells(1, 8) Then
'check if column 1 value is before I1
If Cells(n, 1) <= Round(Cells(1, 9).Value, 10) Then
Cells(n, 2) = 1
End If
End If
Next n


rounding any repeating values drops any repetition after the round point and
forces the number to be less than what I am comparing it to. Rounding both
to the same value doesn't seem to work. I am really surprised this behavior
hasn't caused anyone else any problems.


(Earlier I had changed all values to numbers 0-59,100-159,200-259,300-359...
This worked beautifully as far as the >=, <= comparisons were concerned but
took about 5 seconds to loop as compared to less than a second with time
values.)
 
Back
Top