Placing text-to-column data in array

[Arne Hegefors]

In my macro I use the text-to-column function (a recorded macro) to split up
info in a list of cells. The text in the cell can be like: ACGB 4.55 0311. I
split this into three different datas: 1)ACGB 2)4.55 and 3)0311. When
recording a macro I store the info in cells on the spreadsheet. However I
want to store the data in an array and then sort the array based on the last
data ie. 3). Is this possible?

My problem is also that I do not know how big the array needs to be. Let me
further explain what I want to do. I have a list with different bonds that
looks like:
Sec ID Sec Type Ticker
ACGB 4.55 0311 GOV BOND FNMA4.8876
ACGB 4.88 0319 GOV BOND FMCC4.11X

Now I want to sort the list based on the last info in Sec ID, I do that with
text-to-columns. However I want to sort the entier list without having to add
extra columns etc. Does anyone have an idea on how to do this? Please help me
out! Any help appreciated! Many thanks in advance!

 

[Muhammed Rafeek M]

Hi Try this one:

Sub Sort_Asc()
Dim i As Integer, k As Integer
Dim fV, sV, col1, col2, col3
i = 2
ActiveSheet.UsedRange.Select
k = Selection.Rows.Count
Range("A2").Select
Do While i < k
fV = Val(Right(Range("A" & i).Value, 4))
sV = Val(Right(Range("A" & i + 1).Value, 4))
If fV > sV Then
col1 = Range("A" & i).Value
col2 = Range("B" & i).Value
col3 = Range("C" & i).Value
Range("A" & i).Value = Range("A" & i + 1).Value
Range("B" & i).Value = Range("B" & i + 1).Value
Range("C" & i).Value = Range("C" & i + 1).Value
Range("A" & i + 1).Value = col1
Range("B" & i + 1).Value = col2
Range("C" & i + 1).Value = col3
i = 2
Else
i = i + 1
End If
Loop
End Sub