一般模組:
Sub 更新()
Dim Arr, xD, a, a2, n%
Arr = Range([b2], Cells(1, Columns.Count).End(1))
For j = 1 To UBound(Arr, 2)
If Arr(2, j) = "" Then Arr(2, j) = "資料無"
Next
For j = 1 To UBound(Arr, 2)
For j2 = j + 1 To UBound(Arr, 2)
If Arr(2, j) > Arr(2, j2) Then
n = n + 1: a = Arr(1, j): a2 = Arr(2, j)
Arr(1, j) = Arr(1, j2): Arr(1, j2) = a
Arr(2, j) = Arr(2, j2): Arr(2, j2) = a2
End If
Next
Next
Range([b12], Cells(11, Columns.Count).End(1)).ClearContents
Range("b11").Resize(2, UBound(Arr, 2)) = Arr
End Sub
工作表1
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Row = 1 Or .Row = 2 Then Call 更新
End With
End Sub作者: johnny1680 時間: 2021-10-7 11:48
Option Explicit
Sub TEST_1()
Dim Brr, C%, j%, i&, xR As Range
'↑宣告變數:Brr是通用型變數,(C,j)是短整數變數,
'i是長整數變數,xR是儲存格變數
C = Cells(1, Columns.Count).End(xlToLeft).Column
'↑令C這短整數變數是 第1列最右側有內容儲存格欄號
If C <= 2 Then Exit Sub
'↑如果C變數<=2!就結束程式執行
Set xR = Range([A2], Cells(1, C))
'↑令xR這儲存格變數是 [A2]到(第1列第C變數欄儲存格),
'令xR這儲存格變數是這範圍儲存格(物件變數)
Brr = xR
'↑令Brr這通用型變數是 二維陣列,以xR變數(儲存格值)帶入
For i = 1 To UBound(Brr)
'↑設順迴圈!i從1到Brr陣列縱向最大索引號列
For j = 1 To UBound(Brr, 2)
'↑設順迴圈!j從1到Brr陣列橫向最大索引號欄
Brr(i, j) = Format(Brr(i, j), "000|")
'↑令i迴圈列j迴圈欄Brr陣列值是指定文字格式的字串
'指定文字格式的字串:若原是數字!就補0成3碼連接"|"
Next
Next
With xR.Offset(4, 0)
'↑以下是關於xR變數向下偏移4列範圍儲存格的程序
.Value = Brr
'↑令該範圍儲存格值是Brr陣列值
.Offset(0, 1).Sort KEY1:=.Item(2, 1), _
Order1:=1, Key2:=.Item(1), Order2:=1, _
Orientation:=xlLeftToRight
'↑令該範圍儲存格向右偏移1欄範圍儲存格,
'做兩層次橫向順排序:
'第1層次以該範圍內下方2列第1欄儲存格為排序基準,
'第2層次以該範圍內第1格儲存格為排序基準
.Replace "|*", "", Lookat:=xlPart
'↑令該範圍儲存格值做文字置換:
'將"|"符號(含)右側的所有文字 置換為空字元
End With
End Sub
