Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
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 "wright" wrote in message ... | 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
On May 19, 4:10*pm, wright wrote:
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. 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
On May 19, 4:38*pm, JW wrote:
On May 19, 4:10*pm, wright wrote: 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. 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
Thank you. This seems to work.
"Jim Rech" wrote: 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 "wright" wrote in message ... | 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
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. "JW" wrote: On May 19, 4:10 pm, wright wrote: 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. 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
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) |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
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 "wright" wrote in message ... | 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) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Time comparison not correct
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.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtract hours from a time and get the correct time | Excel Worksheet Functions | |||
Time comparison problem | Excel Programming | |||
Time Comparison | Excel Programming | |||
Time comparison formula | Excel Discussion (Misc queries) | |||
Time comparison | Excel Programming |