Extracting Numbers From Text Range And Sorting Numerically

[jon87]

Hi Guys,

I am having a little novice problem here :S, I am trying to sort a table based on the values in column A, from row 4 onwards. The Values in these cells are rather varied. I have tried using the below code for this;

Please Login or Register  to view this content.

But the resulting sort puts them in this order;

1-1
1-2
2-3
2-4
1-5
1-6
1-7
2-7
1-8
2-9
1-12
1-1a
1-1b
121v
123v
129v
130v
131v
132v
133v
134v
135v
139v
140v
147v
148v
149v
150v
151v
153v
156v
165v
166v
167v
168v
169v
170v
171v
172v
173v
17v
96v
F120v
X141v


And I would like for it to sort numerically as to give the following result;


1-1
1-1a
1-1b
1-2
1-5
1-6
1-7
1-8
1-12
2-3
2-4
2-7
2-9
17v
96v
F120v
121v
123v
129v
130v
131v
132v
133v
134v
135v
139v
140v
X141v
147v
148v
149v
150v
151v
153v
156v
165v
166v
167v
168v
169v
170v
171v
172v
173v


Any help that you guys could offer would be amazingly appreciated, as this has been driving me up the wall for several hours :D.

Cheers

Jon

[MarvinP]

Hi Jon,

Try
DataOption:=xlSortNormal
and see what happens.

When I do this by hand I get a Sort Warning Dialog. When I click the second option of "Sort numbers and numbers stored as text separately" I get what you want. I'm using 2010 Excel.

[jon87]

Hi Marvin,

Unfortunately that gives me a different, but still incorrect result;


1-1
1-12
1-1a
1-1b
1-2
121v
123v
129v
130v
131v
132v
133v
134v
135v
139v
140v
147v
148v
149v
1-5
150v
151v
153v
156v
1-6
165v
166v
167v
168v
169v
1-7
170v
171v
172v
173v
17v
1-8
2-3
2-4
2-7
2-9
96v
F120v
X141v


I'm using 2007, and don't receive an error message :S

Thanks any way mate

[MarvinP]

I think the problem is that some of your cells are numbers and others are text. If you can make all of them TEXT then it might work.

You really need to attach a sample workbook for us to get further with this problem.
To attach a sample, click on "Go Advanced" below this message area and then on the Paper Clip Icon above the message area.

[jon87]

I have tried formatting them all to text or all to number :S, I will attach a copy of the workbook in test phase for ya (All N/A functions and equations removed).

To give an overall breakdown of what the entire macro is doing;

Upon clicking command button on "Generate" Sheet, the workbook compiles all data from the other sheets and puts them together in a newly created "Results" Sheet, removes rows that begin with blank cells or cells that contain the prefix "Blk". It is on this results sheet that I need for the macro to also sort them into the numerical order I said at the start.

If you could solve this for me I will love you forever lol, been bugging me for aaaages now haha

Cheers

Jon

[jon87]

bumpety bump

[MarvinP]

Hi,

Just got up...

Find the attached with a Sort as I suggested above. This is as good as I can do. No code used.

[nilem]

Hi Jon, and try this
I added the Module2, and a little (just a little) has changed the procedure 'Results'

[jon87]

Ok thanks for tyring mate, if anyone else has any tips on how to improve upon this it would be much appreciated.

Jon

[jon87]

Nilem, You are a legend, thats perfect thank you mate.