用VBA如何排序数组？

foxlandy · 发表于 2009-5-21 22:43

有一个动态数组设它为Array（03，02，04，05，07，09，10，12，11，13，19，21）数组是随机生成的2位数编号.

我需要用VBA重新整理该数组，按照从小大大的顺序重排，并且把连续数字用00-99的方式简写出来，不连续的用逗号“，”连接。
并将结果02-05，07，09-13，19，21显示在Sheet1的[A1]单元格.
请问程序如何写？

请各位懂数组处理的大师出来帮忙。
*****声明：这个数组是产品的分切编号，需要在装箱后把对应的分切号按照从小到大的顺序进行简化排列，并且打印到标签上。
并非什么双色球之类的东西。。。

[ 本帖最后由 foxlandy 于 2009-5-21 22:50 编辑 ]

foxlandy · 发表于 2009-5-21 23:14

哪位大仙先帮忙写个Array()数组排列的例子？

zhaogang1960 · 发表于 2009-5-21 23:46

请测试:
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")
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
         i = i + 1
         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

fdd · 发表于 2009-5-22 06:44

原帖由 zhaogang1960 于 2009-5-21 23:46 发表
请测试:
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" ...

这一段：
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
好像就是“冒泡排序法”吧!?学习！

foxlandy · 发表于 2009-5-22 09:52

听说用希尔排序法速度最快?
但是像我们数组内数据个数少的话,用什么方法几乎都一样快.

glctgu · 发表于 2009-5-22 10:14

如果通过排序实现整行变动该怎么办啊？

foxlandy · 发表于 2009-5-22 10:16

排序有所了解,那如何将排序的内容简化显示呢?

蓝桥玄霜 · 发表于 2009-5-22 10:31

其实已经按楼主要求显示在A1单元格了。
或者把
[a1] = s
改为：
MsgBox s
1960的代码很好，学习了。

[ 本帖最后由蓝桥玄霜于 2009-5-22 10:33 编辑 ]

foxlandy · 发表于 2009-5-22 12:52

原帖由 zhaogang1960 于 2009-5-21 23:46 发表
请测试:
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" ...

为何我自己增加了a的arrray数据,结果就提示下标越界?
a = Array("03", "02", "04", "05", "07", "09", "10", "12", "11", "13", "19", "21" )
变成
a = Array("03", "02", "04", "05", "07", "09", "10", "12", "11", "13", "19", "21","22")
就提示下标越界了.

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)
Set r = Union(r, Range("a1" & a(i)))
Next
MsgBox Replace(Replace(r.Address, "$A$1", ""), ":", "-")
End Sub

复制代码

		自动登录	找回密码
密码			免费注册