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
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. |
#5
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) |
#6
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) |
#7
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 |
#8
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 |
#9
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 |
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 |