Summer
01-28-2016, 05:03 AM
Hi everybody!
i'm fairly new to vba programming, so please bear with me : )
I am trying to write a macro that checks whether there is a timestamp missing in a column that looks like that:
00:00
00:10
00:20
00:30
...
23:50
This succession could be complete or there could be random times missing. All the values will be in these 10min intervals though.
My basic Idea is this:
Dim row as double
dim time as date
dim test as date
Row = 1
Time = 0
Test = 0
x = 0
Do Until Row > 144
Time = Cells(Row, 1)
Test = DateAdd("n", x * 10, 0)
If Time = Test Then
Row = Row + 1
Else
Cells(Row, 3) = "ERROR"
Row = Row + 1
x = x + 1
End If
x = x + 1
Loop
So whenever the code notices that the times are not the same, I want the message "ERROR" to appear and the value for x needs to be adjusted (incremented by 2 instead of 1) so the following timestamps can be tested.
This does work until x =10, so the minutes added = 100, and the time = 01:40.
.
At this point the script finds that, for whatever reason, the values are not identical.
I had the the script output the values of "dateadd" in a different column and compared the cells in different formats to make sure all decimal places are the same: they are.
Same thing happens for x=11, time = 01:50
It works with ALL of the other 10 minute intervals, up until 23:50!
(In this case, I only incremented x by 1 each time, otherwise this couldnt be tested)
I also tried erased the 01:40 cell and input the value manually to make sure nothing else is in there, didn't work.
EDIT: I even copied the 01:40 & 01:50 output from "dateadd" into the original timeline, and run it again, so it would compare the exact same entries. Still doesn't work.
Does anybody have a clue why this doesn't work?
If you need more info, let me know : )
Thanks a lot, guys!
i'm fairly new to vba programming, so please bear with me : )
I am trying to write a macro that checks whether there is a timestamp missing in a column that looks like that:
00:00
00:10
00:20
00:30
...
23:50
This succession could be complete or there could be random times missing. All the values will be in these 10min intervals though.
My basic Idea is this:
Dim row as double
dim time as date
dim test as date
Row = 1
Time = 0
Test = 0
x = 0
Do Until Row > 144
Time = Cells(Row, 1)
Test = DateAdd("n", x * 10, 0)
If Time = Test Then
Row = Row + 1
Else
Cells(Row, 3) = "ERROR"
Row = Row + 1
x = x + 1
End If
x = x + 1
Loop
So whenever the code notices that the times are not the same, I want the message "ERROR" to appear and the value for x needs to be adjusted (incremented by 2 instead of 1) so the following timestamps can be tested.
This does work until x =10, so the minutes added = 100, and the time = 01:40.
.
At this point the script finds that, for whatever reason, the values are not identical.
I had the the script output the values of "dateadd" in a different column and compared the cells in different formats to make sure all decimal places are the same: they are.
Same thing happens for x=11, time = 01:50
It works with ALL of the other 10 minute intervals, up until 23:50!
(In this case, I only incremented x by 1 each time, otherwise this couldnt be tested)
I also tried erased the 01:40 cell and input the value manually to make sure nothing else is in there, didn't work.
EDIT: I even copied the 01:40 & 01:50 output from "dateadd" into the original timeline, and run it again, so it would compare the exact same entries. Still doesn't work.
Does anybody have a clue why this doesn't work?
If you need more info, let me know : )
Thanks a lot, guys!