If command not matching Timecodes

[fidgen]

Hi, I wrote this macro to seach down a worksheet by row, summarising the rows by time. It creates a row 2:2 with timecode 0:00:00 in and proceeds to sum all other rows with timecode 0:00:00 to 0:07:00 into that row. Then it creates a row 3:3 and sums all other rows with timecodes 0:08:00 to 0:22:00 etc basically lumping the data together into groupings of 15 minutes.

Or thats the theory. The macro works fine in that it creates a page of created rows each 15 minutes later than the last, but it totally fails to pick up and match the times in the cells it should be comparing, it just rolls over the top.

All the data rows have times in column B, so once its created a new row and put 0:00:00 in B2 it should look to C2 to see if the time in that cell matches B2. EVEN IF IT DOES, it just ignores it and carries on.

I've tried formatting the times several ways - decimalisation, various placings of the ":" in 0:00etc but nothing works and I plainly can't see why.

Help?




Many thanks,
Hugh

Sub whywontyoubloodyworkyougit()

C = TimeValue("00:00:00")

For A = 2 To 1000
    If Sheets("corvid").Cells(A, 1) = "" Then
        Exit For
    End If

    If C >= TimeValue("23:59:00") Then
        Exit For
    End If

            Rows(A).Select
            Selection.Insert Shift:=xlDown
            Sheets("corvid").Cells(A, 2) = C

            For E = 1 To 7
                If Sheets("corvid").Cells(A + 1, 2) = C Then
                    For D = 3 To 100
                            If Sheets("corvid").Cells(A + 1, D) = "" Then
                                Exit For
                            End If
                        Sheets("corvid").Cells(A, D) = Sheets("corvid").Cells(A + 1, D) + Sheets("corvid").Cells(A, D)
                        Sheets("corvid").Cells(A, 1) = Sheets("corvid").Cells(A + 1, 1)
                    Next 'D
                        Rows(A + 1).Select
                        Selection.Delete Shift:=xlUp
                End If
                C = C - TimeValue("00:01:00")
            Next 'E

            For F = 1 To 15
                If Sheets("corvid").Cells(A + 1, 2) = C Then
                    For D = 4 To 100
                            If Sheets("corvid").Cells(A + 1, D) = "" Then
                                Exit For
                            End If
                        Sheets("corvid").Cells(A, D) = Sheets("corvid").Cells(A + 1, D) + Sheets("corvid").Cells(A, D)
                        Sheets("corvid").Cells(A, 1) = Sheets("corvid").Cells(A + 1, 1)
                    Next 'D
                        Rows(A + 1).Select
                        Selection.Delete Shift:=xlUp
                End If
                C = C + TimeValue("00:01:00")
            Next 'F
            
            For G = 1 To 7
                C = C + TimeValue("00:01:00")
            Next 'G
            
Next 'A

End Sub

[/code]

 

[dcline]

If your timecodes are not matching, even when they should, you can convert them to rounded doubles to compare them (I think 14 places decimal places will get you the matches you need)

I ran into a similiar problem when comparing 8:00 am to 8:00 am, as 8 am is exactly 1/3 of the day, the actual value was .333333333333333333333333.......
Excel seems to have a problem comparing numbers of this type. Round it and you should be fine.

I was able to get the following code to compare correctly

            For F = 1 To 15
                Test = Round(Sheets("corvid").Cells(A + 1, 2), 14)
                TestCompare = Round(C, 14)
                Rem If Sheets("corvid").Cells(A + 1, 2) = C Then
                If Test = TestCompare Then