Array sort
 

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 ***

Array sort
 

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


"kenrock"
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

Array sort
 

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 ***

Array sort
 

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" 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




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

Array sort
 

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

Array sort
 

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 ***

Array sort
 

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