Need help with sort macro.

[kruzin24]

I have a worksheet that I run a list of macros from, and a sheet called Orders.xls that is edited by the worksheet. It has 4 columns, and a maximum of 3000 rows. I recorded a macro to sort the invoices by column D, by product number. This macro does not error, but does not sort any data. An example of what it sorts is below, also with the macro I am trying.

Data needing sorted:

PRODUCT PRODUCT PRODUCT PRODUCT
CODE NAME COUNT NUMBER
----- ----- ----- -----
PHT PHOTOS 129 8
PRN PRINTER 1 139
PHN PHONE 3 23

Result of sort:

PRODUCT PRODUCT PRODUCT PRODUCT
CODE NAME COUNT NUMBER
----- ----- ----- -----
PHT PHOTOS 129 8
PHN PHONE 3 23
PRN PRINTER 1 139

Windows("ORDERS.xls").Activate
    Sheets("ORDERS").Range("A4:D3000").Select
    Cells.Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

If anyone is able to assist, it would be greatly appreciated. Thank you!

[Killer42]

I'm not really familiar with using the Sort from VBA. However, I have a sneaking suspicion that your problem may be related to your use of Key1:=Range("D4"). I wouldn't claim to know what it should be, though. If someone else doesn't jump in, I'll look into this tomorrow morning, when I have the tools available.

[kruzin24]

I've tried changing the Range("D4") to about everything I could imagine, from the range of the cells to be edited, to the cell which is the header row, with no avail. I appreciate you taking the time to assist with this!

[Killer42]

I've tried changing the Range("D4") to about everything I could imagine, from the range of the cells to be edited, to the cell which is the header row, with no avail. I appreciate you taking the time to assist with this!

Usually the easiest way to get the parameters right (or at least get some ideas on what to do with them) is to record a macro, and do the action (in this case the sort) yourself. Then the macro will contain the code to do what you want.

Having just tried this myself, I see that your Key seems fine (sorry about that). However, just to see whether it makes any difference, could you try this modified version? (I'm assuming for the moment that you're already in the ORDERS sheet)

Range("A4:D3000").Sort Key1:=Range("D4"), Order1:=xlAscending, Header:= _
  xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
  DataOption1:=xlSortNormal

As you can see, I've only changed the way the cells are selected.

[Killer42]

Ok, two points.

Firstly, I've been playing with various aspects of your Sort command, and your syntax appears to work perfectly well. Forget what I said about the range selection.

Secondly, I've just re-read the original question, and as far as I can see, your sort did work. Why do you think it doesn't?

The only possible problem I can see is that perhaps you wanted an alpha rather than a numeric sort, in which case you need to set the format of the cells to Text.

In other words, it appears that your sequence 8, 139, 23 was correctly sorted into 8, 23, 139 (ascending numbers). If you want to treat them as text, then I guess you'd expect the sequence to become 139, 23, 8.

[kruzin24]

Forgive me for the wording on my original question, when I quoted Result of Sort, I meant that this was the result I was looking for. When I run the sort, it actually does nothing. The data looks exactly the way it did before I ran it. I'm going to attempt your modified version, and I will let you know of the result as soon as possible. Thanks again!

[Killer42]

Forgive me for the wording on my original question, when I quoted Result of Sort, I meant that this was the result I was looking for. When I run the sort, it actually does nothing. The data looks exactly the way it did before I ran it. I'm going to attempt your modified version, and I will let you know of the result as soon as possible. Thanks again!

Oh, ok.

I don't think it will help, though. I played around and the different ways of selecting the cells and so on didn't make any difference.

Um... is the sheet protected? Perhaps sorting is not allowed at that point.

Oh, a quick tip. If you want columns to line up in a post (like those in your original post that started this thread), put [C O D E] tags around the relevant part of the text, so it uses a non-proportional font. For example...


Data needing sorted:

PRODUCT    PRODUCT        PRODUCT    PRODUCT 
CODE    NAME        COUNT    NUMBER
-----   -----           -----   -----
PHT    PHOTOS        129    8
PRN    PRINTER        1    139
PHN    PHONE        3    23

Desired Result of sort:

PRODUCT    PRODUCT        PRODUCT    PRODUCT 
CODE    NAME        COUNT    NUMBER
-----   -----           -----   -----
PHT    PHOTOS        129    8
PHN    PHONE        3    23
PRN    PRINTER        1    139

[solomonp]

Hi-

I usually do my .xl VBA sorts like this:

With Sheets("YourSheetName")
    Set r1 = .Range("A4:D3000")
    r1.Select
    Selection.Sort Key1:=.Cells(4,4), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End With
 

This specifies a few things the XL macro recorder doesn't, and specifies them a bit differently (as I recall) and seems to be more robust.

Leo

[kruzin24]

Killer, thanks for the input on the [ C O D E ] tags, and for assisting with my sort macro. I tried the modified version, and it did not work. However, I tried solomonp's version, and that did work. Seems the key my recorder was using was not correct. Thank you both so much for your assistance with this, I greatly appreciate it!

[Killer42]

Glad that worked out.

I guess we've both learned something from this experience.

[blatora]

if you're still checking, this is the REAL reason i believe your sort isn't working they way you intend. the fact that your "numbers" are left-justified in the cells leads me to believe that they're not formatted as numbers at all, but are actually text. so as far as excel is concerned, the sort works because "words" starting with 3 are greater than "words" starting with 2, etc. what you want to do is edit your code to look like this

Range("A4:D3000").Sort Key1:=Range("D4"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers

(all i did was change DataOption1 from xlSortNormal to xlSortTextAsNumbers). and i've had bad luck with setting header to xlGuess. probably safer to use xlNo if you know there's no header there. hope that helped!