Incorrect Value being Applied To Cell From Code

[Ark68]

Consider this sample of data from my database:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Record ID$	Date	CONTRACT	Ammnt$	Type$	Program$	Customer$	Facility$	SubFacility$	Facility B$	FacType$	Unit$	CLASS$	Start	End
25	41442064	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 1	RIM Park Outdoor FacilitiesBeach Volleyball Court 1	Court	1	B	6:00 PM	7:15 PM
26	41442064	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 1	RIM Park Outdoor FacilitiesBeach Volleyball Court 1	Court	1	B	7:15 PM	8:00 PM
27	41442065	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 2	RIM Park Outdoor FacilitiesBeach Volleyball Court 2	Court	2	B	6:00 PM	7:15 PM
28	41442065	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 2	RIM Park Outdoor FacilitiesBeach Volleyball Court 2	Court	2	B	7:15 PM	8:00 PM
29	41442066	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 3	RIM Park Outdoor FacilitiesBeach Volleyball Court 3	Court	3	B	6:00 PM	7:15 PM
30	41442066	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 3	RIM Park Outdoor FacilitiesBeach Volleyball Court 3	Court	3	B	7:15 PM	8:00 PM
31	41442067	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 4	RIM Park Outdoor FacilitiesBeach Volleyball Court 4	Court	4	B	7:15 PM	8:00 PM
32	41442067	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 4	RIM Park Outdoor FacilitiesBeach Volleyball Court 4	Court	4	B	7:15 PM	8:00 PM
Sheet1

And this code ...

    With wshhold
        If .FilterMode Then .ShowAllData
        lr = .Range("A" & .Rows.Count).End(xlUp).Row
        With .Range("CT2:CT" & lr)
            .Formula = "=IF($CS3<>$CS2, 0, IF(ABS($N3-$O2)<=TIME(0,15,0), ""DUP"", 0))"
            .Value = .Value
        End With

        For Rw = lr To 2 Step -1 '!!!!! WATCH THIS LINE !!!!!
            If .Range("CT" & Rw) = "DUP" Then
                .Range("O" & Rw - 1).Value = .Range("O" & Rw).Value
                lmrg = lmrg + 1
            End If
        Next Rw

        With .Range("CT3:CT" & lr)
            On Error Resume Next
            .SpecialCells(xlConstants, 2).EntireRow.Delete xlShiftUp
            .Clear
        End With
            MsgBox lmrg & " records merged."
            wshvar.Range("A37").Value = lmrg
            If .FilterMode Then .ShowAllData
    End With

The idea is to find two or more records at the same facility that start within 15 minutes of the one before it ending, to become one record. An example using the data above.

146031RIM Park Outdoor FacilitiesBeach Volleyball Court 1 has two records. The first is 6:00PM-7:15PM, and the second, is 7:15PM-8:00PM

The code is intended to merge the two records into one time period for 6:00PM-8:00PM. Basically, it involves taking the later of the end times and overwriting the end time of the first record, and eliminating all but the first.

In this case, the two records would become one:
146031RIM Park Outdoor FacilitiesBeach Volleyball Court 1 6:00PM-8:00PM

When the code completes ... this is the result:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
25	41442064	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 1	RIM Park Outdoor FacilitiesBeach Volleyball Court 1	Court	1	B	7:15 PM	7:15 PM
26	41442065	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 2	RIM Park Outdoor FacilitiesBeach Volleyball Court 2	Court	2	B	7:15 PM	7:15 PM
27	41442066	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 3	RIM Park Outdoor FacilitiesBeach Volleyball Court 3	Court	3	B	7:15 PM	7:15 PM
28	41442067	17-Jun-13	146031	0	CR	CARL Beach Volleyball 2013	RIM Customer Service	RIM Park Outdoor Facilities	Beach Volleyball Court 4	RIM Park Outdoor FacilitiesBeach Volleyball Court 4	Court	4	B	7:15 PM	8:00 PM
HOLDING

The times are wrong. (Columns N and O). They should be 6:00PM - 8:00PM.

Is anyone able to figure out my error? Is more information needed?

 

[Special-K99]

Columns N and O are not visible

 

[Ark68]

Hi Special K-99 ...

I don't quite understand your comment. They are visible on the data worksheet, no columns have been hidden.

 

[Ark68]

I suspect it may have something to do with this:

With .Range("CT2:CT" & lr)
            .Formula = "=IF($CS3<>$CS2, 0, IF(ABS($N3-$O2)<=TIME(0,15,0), ""DUP"", 0))"
            .Value = .Value
        End With

If we look at column CS, with our original data ... I believe things will work if CS26, CS28, CS30 and CS32 are assigned DUP. For some reason, CS25, CS27, CS29 and CS31 are being labelled the DUPs. Of the multiple rows, the first one shouldn't be DUP'd.

 

[Ark68]

I think I got it ....

With .Range("CT2:CT" & lr)
            .Formula = "=IF($CS2<>$CS1, 0, IF(ABS($N2-$O1)<=TIME(0,15,0), ""DUP"", 0))"
            .Value = .Value
End With