Issue with macro, the columns are not sort

N

Naval_JOR

New Member
Joined
Jun 7, 2018
Messages
2
Hi Guys,
I just came to this forum out of millions of Excel forums, I'm quite new with VBA because I dont use that much. Well , here is my issue. I have a macro which is reading angles degrees from 0 to 360 , increment 15 degrees. The macro is reading well the file but somehow is given me

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]150[/TD]
[/TR]
[TR]
[TD="align: right"]165[/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[/TR]
[TR]
[TD="align: right"]195[/TD]
[/TR]
[TR]
[TD="align: right"]210[/TD]
[/TR]
[TR]
[TD="align: right"]225[/TD]
[/TR]
[TR]
[TD="align: right"]240[/TD]
[/TR]
[TR]
[TD="align: right"]255[/TD]
[/TR]
[TR]
[TD="align: right"]270[/TD]
[/TR]
[TR]
[TD="align: right"]285[/TD]
[/TR]
[TR]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: right"]315[/TD]
[/TR]
[TR]
[TD="align: right"]330[/TD]
[/TR]
[TR]
[TD="align: right"]345[/TD]
[/TR]
[TR]
[TD="align: right"]360[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]105[/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[/TR]
</tbody>[/TABLE]

which is incorrect I would prefer from 0-360 from small value to great value. Could you please give me a help, I have spent some hours trying to find a solution in google but not luck

part of the macro below

' Convert strings to numbers and seperate








Range("Z12:Z200").Select

Selection.TextToColumns , _
Destination:=Range("Z12:Z200"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True



End Sub

the issue occurs from Range ("Z12:Z200").Select , I believe the selectiontext to Columns needs to be able to recognise the angles from 0 to 360 and sort them out wisely


Kind Regards
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Welcome to MrExcel,

How many columns of data do you have after the text is split into columns?

What is an example value for cell Z12 before running the macro?
 
Upvote 0
Jerry Sullivan said:
Welcome to MrExcel,

How many columns of data do you have after the text is split into columns?

What is an example value for cell Z12 before running the macro?
Click to expand...
Hi, I have only 2 columns of data as you can see below ,

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]0[/TD]
[TD="class: xl65, width: 64, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]105[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]135[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]165[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]195[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]210[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]225[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]240[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]255[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]270[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]285[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]300[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]315[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]330[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]345[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
[TR]
[TD="align: right"]360[/TD]
[TD="class: xl65, align: right"]8.69E-01[/TD]
[/TR]
</tbody>[/TABLE]
but then the macro is not reading properly the columns and is messing up like this

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]150[/TD]
[TD="width: 64, align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]165[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]180[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]195[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]210[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]225[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]240[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]255[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]270[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]285[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]300[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]315[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]330[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]345[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]360[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]75[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]105[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD="align: right"]0.86868[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
To clarify, I'm wanting to understand the exact text in Column Z, before the Text To Columns process runs.

I'm assuming that you have only one Column of data before the Text To Columns runs.
Cell Z12 might hold a value like one of these...
0 0.86868
0 8.69E-01
"0" "0.86868"
"0" "8.69E-01"
"0 0.86868"
"0 8.69E-01"

or something similar.

Also, please check that a stand alone Sub that uses just the code you posted in Post #1 , exhibits that same behavior (ensuring the problem isn't due to another part of your code you haven't posted).
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Text to Columns in Macro
2
Replies
10
Views
538
Alex Blakenburg
A
Z
  • Solved
Date and Time Not Sorting
2
Replies
19
Views
305
MARK858
MARK858
D
  • Question Question
VBA optimization advice
Replies
3
Views
375
bobsan42
bobsan42
A
  • Solved
Looping Through Columns
Replies
3
Views
731
kevin9999
K
Q
  • Question Question
How to remove 400 error from clear even rows/text to columns macro?
Replies
1
Views
348
Joe4
Joe4

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top