Sorting Data Dynamically???

jrneal3 · January 2nd, 2010, 02:29 PM

OK, I'll take my best shot at explaining what I'm trying to do. I'm working on a project for my aunt. I've been successful at everything except sorting the data. I have five columns of data, three of which are dates for deadlines and the other two are markers to show which deadlines have been met.

I've tried using nested for loops for each of the date columns to select a row if that deadline hasn't been met, then insert it where it goes. Once the left date column is sorted I move to the next date column and repeat. Likewise for the third date column.
My code looks like this:

Code:

 
For I = 2 To LastRow Step 1
        If oSH.Cells(I, 7).Value = Empty And oSH.Cells(I, 9) = Empty Then
            For J = 2 To LastRow Step 1
                If Cells(I, 6).Value <= Cells(J, 6).Value And I > J Then
                    Cells(J, 1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                    Cells(I + 1, 1).EntireRow.Copy Destination:=Cells(J, 1).EntireRow
                    Cells(I + 1, 1).EntireRow.Delete Shift:=xlUp
                End If
            Next J
        End If
Next I

This doesn't exactly work because I get a date of 03/15/2010 ahead of 03/11/2010 in the righthand column.

Two questions: 1) Is there a better way to do what I'm trying to do? 2)If not, what am I missing in the existing code?

I can send examples of my sheet if need be.

Thanks,
Justin

gbianchi · January 3rd, 2010, 11:08 AM

MMMM.. you know that excel has a built in sort option??

Also, to see it work in code, just record a macro that do what you need and then use it.

Also, take in mind that to sort dates, the best way is to use them in the form of YYYYMMDD

jrneal3 · January 4th, 2010, 05:21 PM

Well, as engineers often do - I overcomplicated the issue. I may as well have tried to turn 2 + 2 into a differential equation. Anway, issue resolved.