sorting strings

[kdt2006]

Hi,

In column 1 I have some sequences. In column 2 I want to have the alphabetically sorted values for each of the column 1 cells.

Is there a vba sort function I can use for this, or do I have to implement a sorting algorithm such as bubble or insertion sort?

Cheers

 

[Lewiy]

Have you tried using the macro recorder to record yourself performing a sort? That will give you a template of the VBA function required for sorting.

 

[kdt2006]

Hi Lewiy,

I can't (or don't know how to) perform a sort as I want to sort the contents of each cell and not sort the column.

for example, I want to get the following output

Column 1: Column 2:
IVQENNGAV AEGINNQVV
VLLLVVVMM LLLMMVVVV
MACLVPAAT AAACLMPTV

With the absence of a specific sort function for strings in vba, the only way I can think of doing this, is by extracting the value from column 1 and using an algorithm to sort the string.

I really hope there is a built in function as I'm a bit short on time

 

[Lewiy]

Here is an example of a sort which will convert A1 alphabetically and save it in B1. There’s probably a simpler way of doing it but the bubble sort works for me!

Sub SortMe()
Dim c As Long
Dim d As Long
Dim TempStore As String
Dim myLength As Long
Dim SortArray() As String
myLength = Len(Range("A1"))
ReDim SortArray(myLength)
For c = 1 To myLength
    SortArray(c) = Mid(Range("A1"), c, 1)
Next c
For d = 1 To myLength
    For c = 1 To myLength - 1
        If SortArray(c) > SortArray(c + 1) Then
            TempStore = SortArray(c)
            SortArray(c) = SortArray(c + 1)
            SortArray(c + 1) = TempStore
        End If
    Next c
Next d
Range("B1") = ""
For c = 1 To myLength
    Range("B1") = Range("B1") & SortArray(c)
Next c
End Sub

Hope that helps!

 

[kdt2006]

Hey Lewiy,

Thank you soo much. This works great as is pretty fast for 2000 records.

Cheers