Sorting an array

[Cervantes]

Hi;
I've got an array of values, that I need to put in reverse alphabetical order. The problem is that the values are spreadsheet columns, and things have finally pushed into the "AA" realm. Before, I was using this wonderful bit of code from TonyJollans:

Dim arrTemp(), SortedString
ReDim arrTemp(1 To Len(YourString))

For i = 1 To Len(YourString)
    arrTemp(i) = Asc(Mid(YourString, i, 10))
Next
For i = 1 To Len(YourString)
    SortedString = SortedString & Chr(WorksheetFunction.Large(arrTemp, i))

However, now that I've gotten into AA, I can't use Chr and the Large function anymore. (at least, I think that's the problem).

So, now, schmuck that I am, I've gotten myself stuck, where I have to properly reverse-sort the array, and I've got till Friday to do it.

So, the details are: I have a comma-separated value string that contains all the columns I want to do an action to, so the string may be, say "c,b,x,z,y,aa,ac". I need to sort that into an array in reverse order (so it starts with array(1) = AC, array(2) = AA, array(3) = z, and so on).

So what I thought I'd do is make an array of single-character columns (xArray), and make another array of double-char columns(xxArray), that second one only containing the last letter, sort them both, and then joing them up, putting an "A" in front of every value in xxArray.

However, the following code craps out, and tells me it can't use Large. I've been unable to figure out why.

Does anyone know, or do you have a better way than this horrid cludge?

Note: in thise code, xCounter keeps track of the single-char array, xxCounter the double, and Counter the total number. Also note that in the final version, I won't be joining the values into a string, I'll be putting them into a single array and then doing my action by looping through that array. But the string makes it easy to debug The premise of the sorting is the same though.

Sub ArraySort()
Dim Value, xArray(), xxArray(), SortedString, YourString, i, Counter, xCounter, xxCounter
Dim Done As Boolean
YourString = "a,b,c,ac,d,aa"

Counter = 0
xCounter = 0
xxCounter = 0
Done = False
Do Until Done = True
    If InStr(YourString, ",") > 0 Then
        Value = Left(YourString, InStr(YourString, ",") - 1)
        YourString = Right(YourString, Len(YourString) - Len(Value) - 1)
    Else
        Value = YourString
        Done = True
    End If
    If Len(Value) = 1 Then
        xCounter = xCounter + 1
        ReDim Preserve xArray(xCounter)
        xArray(xCounter) = Value
    ElseIf Len(Value) = 2 Then
        xxCounter = xxCounter + 1
        ReDim Preserve xxArray(xxCounter)
        xxArray(xxCounter) = Right(Value, 1)
    Else
        MsgBox ("Error")
    End If
    Counter = Counter + 1
Loop

For i = 1 To xxCounter
    SortedString = SortedString & "A" & Chr(WorksheetFunction.Large(xxArray, i))
Next i
For i = 1 To xCounter
    SortedString = SortedString & Chr(WorksheetFunction.Large(xArray, i))
Next i

MsgBox (SortedString)
End Sub

Thanks! (and please don't laugh)

 

[cLFlaVA]

Something you should look into is getting the column number of each column.

The following will pop a message box showing you the column number:

[tt]
Dim theString as String
Dim theArray, theCol as Variant

theString = "a,b,c,ac,d,aa"
theArray = Split(theString, ",")

For Each theCol In theArray
MsgBox (Range(theCol & "1").Column
Next theCol
[/tt]

As you can see, this will return the numerical value of each column. You should be able to take it from there.

Hope this helps.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[cLFlaVA]

Sorry, that should be:

[tt]MsgBox Range(theCol & "1").Column[/tt]

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[sfvb]

I think this is what you want:

Sub ArraySort()
   Dim arrCol()      As String
   Dim arrTmp()      As Long
   Dim YourString    As String
   Dim SortedString  As String
   Dim strTmp        As String
   Dim lngTmp        As Long
   Dim idx           As Integer
   
   YourString = "a,b,c,ac,d,aa,aw,er,ty,sd,er,ty,rt,fg,hj,zx,kl"
   arrCol = Split(YourString, ",")
   ReDim arrTmp(UBound(arrCol))
   
   'force to 2 characters, and change to a single number
   For idx = 0 To UBound(arrCol)
      strTmp = UCase(Right(" " & arrCol(idx), 2))
      arrTmp(idx) = Asc(Left(strTmp, 1)) * 128 + Asc(Right(strTmp, 1))
   Next
      
   'reverse sort the single number and tru back into 2 characters
   For idx = 0 To UBound(arrTmp)
      lngTmp = WorksheetFunction.Large(arrTmp, idx + 1)
      SortedString = SortedString & Chr(Int(lngTmp / 128)) & Chr(lngTmp Mod 128) & ","
   Next
   
   SortedString = Replace(SortedString, " ", "")
   SortedString = Left(SortedString, Len(SortedString) - 1)
   
   MsgBox (SortedString)
End Sub

Steve

 

[Cervantes]

Hi Steve:
Thanks for the reply. That is indeed exactly what I'm looking for. I'm just having trouble getting it to work with Option Base 1 (which the rest of my code is written with). Any suggestions?

cLFlaVA:
I considered using numbers instead, but the loop this will be working with is going to explicitly reference the columns by letter(as in columns(var:var).select).

-Cerv

 

[jcrater]

Sounds like you need to convert the new code to 1-based arrays.

 

[Cervantes]

Yeah, I just buggered up a bit on the variable names, that's all. A few tweaks required to get it to behave, and it works like a charm.