Hi,

I have a list of appartement numbers. Sometimes it's written 102 o
sometimes 102a and 102b.

E.G

102
103
104a
104b
105
(...)

Actually, if I record a macro to sort those valuesm it puts 104a an
104b at the end of the list, without considering them as numbers whic
are locally before 105. Note that it could be one, two, three or mor
digits. It would be a dynamic lenght. The letter too could be dynami
as well.

Is there a way to tell Excel/VBE to sort intelligently this list?

Thx!

Werner


Code
-------------------


Sub Sorting()

Rows("16:29").Select
Selection.Sort Key1:=Range("A16"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub


-------------------

--
Werne
-----------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...fo&userid=2430
View this thread: http://www.excelforum.com/showthread.php?threadid=38524

You need to create a new column (Call it sort) and make it the text values of
all of the numbers and text. Sort based on this new column and then delete
the column when you are done. Or just convert all of the numbers to text and
then sort.
--
HTH...

Jim Thomlinson


"Werner" wrote:


Hi,

I have a list of appartement numbers. Sometimes it's written 102 or
sometimes 102a and 102b.

E.G

102
103
104a
104b
105
(...)

Actually, if I record a macro to sort those valuesm it puts 104a and
104b at the end of the list, without considering them as numbers which
are locally before 105. Note that it could be one, two, three or more
digits. It would be a dynamic lenght. The letter too could be dynamic
as well.

Is there a way to tell Excel/VBE to sort intelligently this list?

Thx!

Werner


Code:
--------------------


Sub Sorting()

Rows("16:29").Select
Selection.Sort Key1:=Range("A16"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub


--------------------


--
Werner
------------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304
View this thread: http://www.excelforum.com/showthread...hreadid=385246

It's logic, it could work easily. But the, how could I convert th
numbers into text? Do you have a vba code for that

--
Werne
-----------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...fo&userid=2430
View this thread: http://www.excelforum.com/showthread.php?threadid=38524

I converted the numbers in text but it didn't sort correctly the numbers
which have a string character at the end. Maybe my sorting formula in
VBA needs a bit more of precision?


Code:
--------------------

With Worksheets("Formulaire")
'The line used to convert numbers into text
Range("A16:A" & lstRw).NumberFormat = "@"
Rows("16:" & lstRw).Select
Selection.Sort Key1:=Range("A16"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom

Range("A15").Select
End With


--------------------


--
Werner
------------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304
View this thread: http://www.excelforum.com/showthread...hreadid=385246

I've done a search in another forum, ozgrid.com forum and I have found a
post going back to july 20th 2004. Derk, an excel expert, has posted
back then an amazing search macro that works like magic. It resolved
instanly all my problems in an efficient way. Here's the link :

http://www.ozgrid.com/forum/showthre...t+text+numbers

I say thanks to Derk for that code!

Werner


--
Werner
------------------------------------------------------------------------
Werner's Profile: http://www.excelforum.com/member.php...o&userid=24304
View this thread: http://www.excelforum.com/showthread...hreadid=385246

The question you posted there doesn't make much sense to me. The sample
data you show is already sorted on both columns from what I can tell.

--
Regards,
Tom Ogilvy


"Werner" wrote in
message ...

I've done a search in another forum, ozgrid.com forum and I have found a
post going back to july 20th 2004. Derk, an excel expert, has posted
back then an amazing search macro that works like magic. It resolved
instanly all my problems in an efficient way. Here's the link :


http://www.ozgrid.com/forum/showthre...t+text+numbers

I say thanks to Derk for that code!

Werner


--
Werner
------------------------------------------------------------------------
Werner's Profile:
http://www.excelforum.com/member.php...o&userid=24304
View this thread: http://www.excelforum.com/showthread...hreadid=385246