三种方法排序,以第三种工作表自带的排序功能最为简单,这个显示在工作表2中,第二就是冒泡排序,显示在工作表1的U列,第一种取行号排序我写的复杂了,显示在工作表1的Q列,真希望多些人答题目,不管是你还是我,都能广开思路。
- Sub todaypx3() '工作表排序
- Dim arr
- arr = Sheets("Sheet1").Range("i13:k" & Sheets("Sheet1").[k65536].End(3).Row)
- With Sheets("Sheet2")
- .[c1] = "次数"
- .[a2].Resize(UBound(arr), 3) = arr
- .Range("a:c").Sort .[c2], 2, , , , , , 1
- End With
- End Sub
复制代码
- Sub todaypx2() '冒泡排序
- Dim arr, i&, j&, t1%, t2%, t3%
- arr = Sheets("Sheet1").Range("i13:k" & Sheets("Sheet1").[k65536].End(3).Row)
- For i = 1 To UBound(arr) - 1
- For j = i + 1 To UBound(arr)
- If arr(i, 3) < arr(j, 3) Then
- t1 = arr(j, 1): t2 = arr(j, 2): t3 = arr(j, 3)
- arr(j, 1) = arr(i, 1): arr(j, 2) = arr(i, 2): arr(j, 3) = arr(i, 3)
- arr(i, 1) = t1: arr(i, 2) = t2: arr(i, 3) = t3:
- End If
- Next
- Next
- Sheets("Sheet1").[u13].Resize(UBound(arr), 3).Clear
- Sheets("Sheet1").[u13].Resize(UBound(arr), 3) = arr
- End Sub
复制代码
- Sub todaypx1() '取行号排序
- Dim arr, ar(), brr(), i&, m%, hh, n&, ro&
- arr = Sheets("Sheet1").Range("i13:k" & Sheets("Sheet1").[k65536].End(3).Row)
- ReDim ar(0 To UBound(arr))
-
- m = Application.WorksheetFunction.Max(Application.Index(arr, , 3))
- For i = 1 To UBound(arr)
- ar(arr(i, 3)) = ar(arr(i, 3)) & i & ","
- Next
- ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
- For ro = m To 0 Step -1
- If InStr(ar(ro), ",") Then
- hh = Split(Left(ar(ro), Len(ar(ro)) - 1), ",")
- Else: n = n + 1
- brr(n, 1) = arr(ar(ro), 1): brr(n, 2) = arr(ar(ro), 2): brr(n, 3) = ro
- GoTo 100
- End If
-
- For i = 0 To UBound(hh)
- n = n + 1
- brr(n, 1) = arr(hh(i), 1): brr(n, 2) = arr(hh(i), 2): brr(n, 3) = ro
- Next
- 100
- Next
- Sheets("Sheet1").[q13].Resize(n + 100, 3).Clear
- Sheets("Sheet1").[q13].Resize(n, 3) = brr
- End Sub
复制代码
|