Data Sort in Excel 2003 not working properly

[vba317]

I am trying to develop a sort that will sort two columns if one condition is met and another sort if another condition is met. When the sort completes I get some cells in column E that are blank. Also in column E the order is not ascending. I thought the line If a > 0 would cause the sort to skip this sort if the cell had a number in it. Any help would be greatly appreciated.

Tom

Column E
1
1
2
2
3
3
3
4
4
4
5.2
4.0
blank
5.2
6.2
blank
4.0

lic Sub SortDec()
    Dim a As Range
    Dim c As Range
    Dim d As Range
    Dim e As Range
    For Each a In Worksheets("ClientProcessing").Range("E9:E99")
        If a > 0 Then
            '3 key sort
            Selection.Sort Key1:=Range("E9"), Order1:=xlAscending, Key2:=Range("A9") _
                            , Order2:=xlAscending, Key3:=Range("J9"), Order3:=xlAscending, _
                           Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                           DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
        Else
        '2 key sort
            Selection.Sort Key1:=Range("C9"), Order1:=xlAscending, Key2:=Range("A9") _
                            , Order2:=xlAscending, _
                           Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                           DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
        End If
    Next

 

[SkipVought]

Hi,

See you got Header:=xlYes.

Why are you sorting your list 90 times? You actually only see and use the LAST SORT

????

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Are ALL your values in column E numeric?

Do you have ANY values that appear to be numeric. but they are REALLY TEXT (a string of numeric CHARACTERS)?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I thought the line If a > 0 would cause the sort to skip this sort if the cell had a number in it.

If you have numbers in column E taht are positive, then column E will sort, AGAIN AND AGAIN.

Exactly what are you trying to accomplish with this process, in prose, please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

What I am trying to do unsucessfully is two sorts. The first sort is done based on Column E, which I fill in and it is a number, based on the day that I am closing the client. Ex. Day 1 or Day 2 or Day 3 etc.. The second column is an A-Z sort on Column A which is the client initials, the last sort is Column J which is either Y or blank. Yes, if done blank if not.

The second sort are the clients that have not been completed. So the first criteria is only do this sort if Column E is blank. The sort is based on a number that I type in for Col C and than A-Z sort for Column A.

Skip, you are correct that I am looping through way too many times. If there is a better way please let me know and I will do it.

Hope this clarifies the issue.

Tom

 

[SkipVought]

This does not makse sense at all!

Column E is the day (not date) that you close the client, where the client is in column A. So I assume that there are many different clients in this sheet, and that there is one row per client.

So why would you SORT anything at all?

Why not use the AutoFilter, and assuming that your table has headings, first filter the day completed heading on (Blanks) or column J to see the clients that have not been closed, whatever makes sense.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

You are correct there are 90 clients. I get a list of when the clients want to be closed. Column B is the actual day of the week ex. Friday March29,2013. Col C is what day in the closing cycle it is days 1-8. Col A is the clients initials. Col D is the actiual day Friday March29, 2013 and E are the actual days 1-8, that the client was closed. This spreadsheet tracks the current status of all the clients. Initally I tried autofilter but if I did a sort on Col E it would not reset the totals from col C.

Tom

 

[SkipVought]

You keep on throwing other factors into the soup.

What is it that you are trying to do? Forget about telling me you want to sort. Tell me WHY you need to sort. Start at the beginning and explain without Excel or VBA techie terms how the data on this sheet is to be used. You have clients rows that are either open or closed. Do you need statistics regarding opened and closed? Do you just need to report opened and closed?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

OK, I usually just give a chunck at a time but I will give the whole truth and nothing but the truth. I have a list of 90 clients. I get a list three days before the closings start what the agreed order is for each client. So I import that list of clients into Col A the spreadsheet. I need to track what the day of the closing cycle the closing gets done days 1-8. I also want to know the actual day Mon-Fri day, ect . I than need to track the date and number of day that the client actually closed. Keep a running tab of percentage done of clients. In Column F keep track if the status of the client. Active or Windown. I have 5 steps of report processing I do and I need to track where I am with each client. Some clients have adhocs so I also need to track which clients have adhocs and what the adhocs are. Finally I track how long each client takes to run on SQL, in access, and running the reports in excel. So I have a column for time start, time end and total time for each step.

Tom

 

[SkipVought]

So far the only statistic I see is % done or am I missing something?

I hope that you're using Named Ranges based on your column headings. My column E heading is CompDay and column A heading is Clients
[tt]
Pct Complete: =COUNTIF(CompDay,">0")/COUNTA(Clients)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}