Array sort

[kenrock]

Hi, I have some queries about the use of strings and arrays:-
1. Is there a simple way to use VBA Code to sort a string eg "SEARCH" to
"AECHRS" ie is there a worksheet function which can be called?
2. If text is in an array eg myArray1(6) = ("S","E","A","R","C","H"),
how can I produce myArray2(6) = ("A","E","C","H","R","S")?
Any thoughts would be greatly appreciated.
Regards. Kenrock

 

[Jim Cone]

Kenrock,
Is the placement of the E before the C intentional?
Jim Cone
San Francisco, USA


"kenrock" <[email protected]>
wrote in message
Hi, I have some queries about the use of strings and arrays:-
1. Is there a simple way to use VBA Code to sort a string eg "SEARCH" to
"AECHRS" ie is there a worksheet function which can be called?
2. If text is in an array eg myArray1(6) = ("S","E","A","R","C","H"),
how can I produce myArray2(6) = ("A","E","C","H","R","S")?
Any thoughts would be greatly appreciated.
Regards. Kenrock

 

[kenrock]

Hi Jim,
Thanks for the reply and no...it is not intentional.
I spotted it AFTER I hit the Send button. To do it once is annoying -
to do it twice is dumb! It should have read 'ACEHRS'on both occasions.
Apologies, Kenrock

 

[kounoike]

try this:
put any characters into any cell and selecting this cell, run Testarraysort macro.
Testarraysort make array a(i) using this cell's value, then qsort, called
quicksort,
will sort the array, and then it will put out sorted data into cells below the
activecell.

Sub Testarraysort()
Dim a()
Dim l As Long, r As Long, i As Long
ReDim a(Len(ActiveCell.Value) - 1)
For i = 0 To Len(ActiveCell.Value) - 1
a(i) = Mid(ActiveCell.Value, i + 1, 1)
Next
l = LBound(a)
r = UBound(a)
qsort a, l, r
For i = l To r
Cells(ActiveCell.Row + 2 + i, ActiveCell.Column) = a(i)
Next
End Sub

Sub qsort(v As Variant, ByVal left As Long, ByVal right As Long)
Dim i As Long
Dim last As Long
If left >= right Then
Exit Sub
End If
swap v, left, (left + right) \ 2
last = left
i = left + 1
Do While (i <= right)
If v(i) < v(left) Then
last = last + 1
swap v, last, i
End If
i = i + 1
Loop
swap v, left, (last)
qsort v, left, (last - 1)
qsort v, (last + 1), right
End Sub

Sub swap(v As Variant, ByVal i As Long, ByVal j As Long)
Dim tmp
tmp = v(i)
v(i) = v(j)
v(j) = tmp
End Sub

keizi

 

[Dave Peterson]

There are lots of sort routines that you can find via google.

This is one:

Option Explicit
Sub testme()

Dim myArr1 As Variant
Dim iCtr As Long
Dim jCtr As Long
Dim Temp As Variant

myArr1 = Array("S", "E", "A", "R", "C", "H")

For iCtr = LBound(myArr1) To UBound(myArr1) - 1
For jCtr = iCtr + 1 To UBound(myArr1)
If myArr1(iCtr) > myArr1(jCtr) Then
Temp = myArr1(iCtr)
myArr1(iCtr) = myArr1(jCtr)
myArr1(jCtr) = Temp
End If
Next jCtr
Next iCtr

For iCtr = LBound(myArr1) To UBound(myArr1)
MsgBox iCtr & "--" & myArr1(iCtr)
Next iCtr

End Sub

It actually sorts the original array--is that a problem?

 

[kenrock]

Many thanks for your reply and apologies for the delay in coming back to
you. I have been distracted by other things but now I have the time to
get to grips with your response.
Regards, Kenrock

 

[Ken Rock]

There are lots of sort routines that you can find via google.

This is one:

Option Explicit
Sub testme()

Dim myArr1 As Variant
Dim iCtr As Long
Dim jCtr As Long
Dim Temp As Variant

myArr1 = Array("S", "E", "A", "R", "C", "H")

For iCtr = LBound(myArr1) To UBound(myArr1) - 1
For jCtr = iCtr + 1 To UBound(myArr1)
If myArr1(iCtr) > myArr1(jCtr) Then
Temp = myArr1(iCtr)
myArr1(iCtr) = myArr1(jCtr)
myArr1(jCtr) = Temp
End If
Next jCtr
Next iCtr

For iCtr = LBound(myArr1) To UBound(myArr1)
MsgBox iCtr & "--" & myArr1(iCtr)
Next iCtr

End Sub

It actually sorts the original array--is that a problem?

Hi Dave,
Many thanks for your reply and apologies for the delay in coming back to
you. I have been distracted by other things but now I have the time to
get to grips with your response.
Regards, Kenrock