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




*** Sent via Developersdex http://www.developersdex.com ***

[Jim Cone]

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


"kenrock" <[email protected]>
wrote in message
news:[email protected]
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



*** Sent via Developersdex http://www.developersdex.com ***

[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

"kenrock" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

[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 wrote:
>
> 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
>
> *** Sent via Developersdex http://www.developersdex.com ***

--

Dave Peterson

[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

*** Sent via Developersdex http://www.developersdex.com ***

[Ken Rock]

Dave Peterson wrote:
> 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 wrote:
>
>>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
>>
>>*** Sent via Developersdex http://www.developersdex.com ***
>
>
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