PDA

View Full Version : [SOLVED] Strange sort problem



Ismael
02-22-2005, 04:30 AM
Hi,

I have a strange problem with a sort macro.

DRJ, has made for a macro that sort more than one column, the macro works fine, but now with the values that I have paste in the range B9:F20000, a strange situation happen. If you run the macro you will see what I?m saying.

So if any of you guys can give me a help, I appreciate

Thanks in advance,

Best regards

Ismael

Paleo
02-22-2005, 07:54 AM
Hi Ismael,

I used your worksheet and got no errors, worked just fine for me. Which error are you getting?

Ismael
02-22-2005, 09:02 AM
Hi, Paleo

How are you?

In my computer the column doesn't stay sort......

In the attach file you can see how the this stay.

best regards,


Ismael

CBrine
02-22-2005, 09:52 AM
Ismael,
The problem isn't caused by the code(as you stated), it's caused by the format of the data. It looks like you are most likely getting this data from another programs export process. These tend to have non standard formats that excel uses. I've added some extra code to resolve your problem. Give it a try an let us know if you have any problems.

HTH
Cal



Sub Sort()
Range("B9:B20000").Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C9:C20000").Sort Key1:=Range("C9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("D9:D20000").TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
Range("D9:D20000").Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("E9:E20000").TextToColumns Destination:=Range("E9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("E9:E20000").Sort Key1:=Range("E9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("F9:F20000").Sort Key1:=Range("F9"), Order1:=xlAscending, Header:= _
xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Ismael
02-22-2005, 10:16 AM
Hi, CBrine

The code that tou have send me give a message of an error that ypu can see in the attach.

The data that I have paste in the file, is from another excel file, and I made a copy paste special values in the file that you have, so I don't now why this happens.....

If you have any sugestion, please tell me

thanks

Ismael

CBrine
02-22-2005, 11:12 AM
Ismael,
That might be just be a version difference(I'm on 2003), just take out the

TrailingMinusNumbers:=True
from both of the new statements and give it a try.

Cal

Paleo
02-22-2005, 01:19 PM
Hi Ismael,

I am back. Just reproduced your problem. Now I will try to fix it.

Paleo
02-22-2005, 01:30 PM
Hi Ismael,

I think I made it.

Ismael
02-23-2005, 02:17 AM
Hi, Carlos

The last file that you have send me give a message of an error when I run the macro, you can see this error in the picture attach.

But if I do what CBrine, has say and delete ", TrailingMinusNumbers:=True " in the code that CBrine have send me everything work nice.

So thanks to you both.

Best Regards,


Ismael

Paleo
02-23-2005, 04:10 AM
Hi Ismael,

works fine for me. Which version of excel are you using? I use 2003.

Anyway you know you are always welcome.

Ismael
02-23-2005, 04:39 AM
Hi, Paleo


I'am using office 2000, do you thing problem can be from here?

Best Regards,

Ismael

Ismael
02-23-2005, 05:20 AM
Hi, sorry to bother again....

but the code that CBrine made, sort the columns one by one, what I want is sort the values by column D then by E, any then for the next, but if I run the code that CBrine what happen is:

for example I have in b9 - 05:00:45, c9 - 06:00:12, d9 - AZ, e9 - 21 and f9 - 1

after run the macro the values doesn't math.....

So if you guys can see that, I appreciate


Best Regards,

Ismael

CBrine
02-23-2005, 09:02 AM
Ismael,
That's the code that DRJ had put together originaly for you. I was wondering how that would help you, since all your values would be misaligned after the process was executed. I figured there must be a reason for this, so didn't comment on it. Let me take a look at the data, and we will see what we can do.

1. You want to sort ALL Columns by the data in Column D.
2. Next sort all data by Column E.
3. Next sort all the data by Column F.

Let me know if this is a true statement.

Cal

Ismael
02-23-2005, 09:13 AM
Hi CBrine,

Sorry my mistake.

Yes I want to sort the data first by column D, then by column E, and the rest doesn't matter, the only sting is that in the end all values have to match as the are before the sort.

I hope this help you to solve the problem.

Thanks in advance

best regards,

Ismael

CBrine
02-23-2005, 09:23 AM
Ismael,
Here's the code modified to do the sort on D and E and F. Key3 won't make a difference.



Range("D9:D20000").TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2)
Range("E9:E20000").TextToColumns Destination:=Range("E9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
Range("B9:F20000").sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9")

HTH
Cal

Ismael
02-23-2005, 09:49 AM
Hi CBrine,


Thank you very much, now the macro does extacly are I want.

best regards,

Ismael

Paleo
02-23-2005, 04:22 PM
Hi Ismael,

glad you got it working. Just one doubt about this line:


Range("B9:F20000").sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9")


Shouldnt it use range B8 till F20000 instead of B9? At least for me there is a blank row row 8).Should it be like this?


Range("B8:F20000").sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9")

And what about all that color formated cells from row 131 to row 28259? Shouldnt they be deleted?

Shouldnt the code be?


Option Explicit
Sub Sort()
Dim FLin As String
Range("D9:D20000").TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2)
Range("E9:E20000").TextToColumns Destination:=Range("E9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
Range("B8:F20000").Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9")
Range("d7").End(xlDown).Select
With Selection
.Offset(1, 0).Select
FLin = .Row
End With
Rows(FLin & ":65536").EntireRow.Delete
Range("B7").Select
End Sub

Ismael
02-24-2005, 02:26 AM
Hi Paleo,

Yes, I could use the range B8 till F20000 instead of B9 till F20000, but I like to stay 1 empty column between the title (range B7:F7) and the data.

When I deleted the colour formatted in the cells from row 131 to row 28259, I only have a problem that is the data in the last row disappear, in the data that you have the row that contain this data ( 7:00:00, 7:07:27, YS, 9622) will disappear when you run the macro.

I try to add more data to the file and I check that this always happen, I just don't understand why?

Thanks for your help :beerchug:


Best regards,


Ismael


PS: Muito obrigado pela disponibilidade que tens demonstrado :thumb
Um grande abra

Paleo
02-24-2005, 08:57 AM
Hi Ismael,

problem solved, sorry for the goofed. The new code is:


Option Explicit
Sub Sort()
Dim FLin As String
Range("D9:D20000").TextToColumns Destination:=Range("D9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2)
Range("E9:E20000").TextToColumns Destination:=Range("E9"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
Range("B9:F20000").Sort Key1:=Range("D9"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, Key2:=Range("E9"), Key3:=Range("f9")
Range("d9").End(xlDown).Select
With Selection
.Offset(1, 0).Select
FLin = .Row + 1
End With
Rows(FLin & ":65536").EntireRow.Delete
Range("B7").Select
End Sub


P.S.: Pode contar sempre comigo e quando puder visite o site na minha assinatura. Sou o administrador dele e l? temos uma ?rea para d?vidas em portugu?s.

Ismael
02-24-2005, 09:30 AM
Hi, Carlos

Once more thanks for the help.

I already put the new code.

Best Regards,

Ismael


PS: J? fui ao teu site e j? fiz o registo, penso em breve visita-lo com maior frequ?ncia, e quem sabe poder ajudar nas quest?es, pois tb n?o sou grande expert em EXCEL, mas tenciono l? chegar.

Um abra

Paleo
02-24-2005, 09:31 AM
Hi Ismael,

glad I helped you out.

P.S.: Obrigado, contamos com a tua participa??o!

Abra?o.