用VBA如何排序数组？

foxlandy

原帖由 northwolves 于 2009-5-22 13:09 发表
根据你的特殊数据，可以这样实现：
Sub macro1()
Dim a, r As Range
a = Array("03", "02", "04", "05", "07", "09", "10", "12", "11", "13", "19", "21")
Set r = Range("a1" & a(0))
For i = 1 To UBound(a) ...

楼上的不错,不过好像借助于EXCEL的本身的功能了.

zhaogang1960

northwolves 版主的代码简练，好好学习
原来代码确实忽略了最后两位相连的情况，纠正如下：
Sub aaa()
    Dim a, temp As String, c As Integer, i As Integer, j As Integer, m As Integer, s As String
    a = Array("03", "02", "04", "05", "07", "09", "10", "12", "11", "13", "19", "21", "22")
    c = UBound(a)
    For i = 0 To c - 1
        For j = i + 1 To c
            If Val(a(i)) > Val(a(j)) Then
                temp = a(j)
                a(j) = a(i)
                a(i) = temp
            End If
        Next j
    Next i
    s = a(0)
    For i = 1 To c
        m = 0
        Do While Val(a(i)) = Val(a(i - 1)) + 1 And i <= c
            i = i + 1
            If i > c Then
                m = m + 1
                Exit Do
            End If
            m = m + 1
        Loop
        If m > 0 Then
            i = i - 1
            s = s & "-" & a(i)
        Else
            s = s & "," & a(i)
        End If
    Next
    [a1] = s
End Sub

走在最后瞧

原帖由 northwolves 于 2009-5-22 13:09 发表
根据你的特殊数据，可以这样实现：
Sub macro1()
Dim a, r As Range
a = Array("03", "02", "04", "05", "07", "09", "10", "12", "11", "13", "19", "21")
Set r = Range("a1" & a(0))
For i = 1 To UBound(a) ...

学习了，真是对EXECL的灵活运用，实在是佩服！

northwolves

其实，循环也可省去的：

1. 
   
2. Sub macro1()
   
3. Dim a, r As Range
   
4. a = Array("03", "02", "04", "05", "07", "09", "10", "12", "11", "13", "19", "21", "22")
   
5. Set r = Union([a1], Range("a1" & Join(a, ",a1")))
   
6. MsgBox Mid(Replace(Replace(r.Address, "$A$1", ""), ":", "-"), 2)
   
7. End Sub
   

复制代码

fdd

原帖由 northwolves 于 2009-5-22 17:20 发表
其实，循环也可省去的：
Sub macro1()
Dim a, r As Range
a = Array("03", "02", "04", "05", "07", "09", "10", "12", "11", "13", "19", "21", "22")
Set r = Union([a1], Range("a1" & Join(a, ",a1")))
Msg ...

经典，堪称灵活运用的典范！学习。

xiangchendub

狼版的粉丝来了，支持。
顺便让fdd兄电一下。

have a nice weekend

mobuli

呵呵，楼上的刚被电，我也试试。
狼兄的代码真好

yanjie

学习一下！

Zamyi

如果返回是数值，一句排序：
[b1:b12] = [SMALL(VALUE({"03"; "02"; "04"; "05"; "07"; "09"; "10"; "12"; "11"; "13"; "19"; "21"}),ROW(A1:A12))*ROW(A1:A12)^0]
或者
[a15:l15] = [SMALL(VALUE({"03", "02", "04", "05", "07", "09", "10", "12", "11", "13", "19", "21"}),column(a15:l15))*column(a15:l15)^0]

[ 本帖最后由 Zamyi 于 2009-5-22 23:19 编辑 ]

Zamyi

原帖由 northwolves 于 2009-5-22 17:20 发表
其实，循环也可省去的：
Sub macro1()
Dim a, r As Range
a = Array("03", "02", "04", "05", "07", "09", "10", "12", "11", "13", "19", "21", "22")
Set r = Union([a1], Range("a1" & Join(a, ",a1")))
Msg ...

太欣赏狼版！