我的Excel包含三列數據

我有一個Excel裡面有三列資料。我想透過VB實現排序功能

Sub Sort1()

ActiveSheet.Sort.SortFields.Clear

ActiveSheet.Sort.SortFields.Add Key:=Range(＂A2:A＂ & Range(＂A1＂).CurrentRegion.Rows.Count) _

, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

ActiveSheet.Sort.SortFields.Add Key:=Range(＂B2:B＂ & Range(＂A1＂).CurrentRegion.Rows.Count) _

, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveSheet.Sort

.SetRange Range(＂A1:C＂ & Range(＂A1＂).CurrentRegion.Rows.Count)

.Header = xlYes

.MatchCase = False

#.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

End Sub

VB數組排序問題

如果在excel表格的A列中依序輸入0-11共12個數字，在B列中對應地輸入12個隨機數，然後按B列排一下序，A列中的數字就變成隨機排列的了

在這裡使用同樣的思路，只不過把二維表格換成二維數組即可，程式碼如下：

Dim Matrix(0 To 11, 0 To 1) As Single

Dim i As Integer, j As Integer

Dim sngTemp As Single

#Randomize

'初始化數組，使每個「行」第一個數字為行號，第二個數字為隨機大小的數字

For i = 0 To 11

Matrix(i, 0) = i

Matrix(i, 1) = Rnd(100)

Next

Text1.Text = ＂＂

'由於陣列較小，這裡使用冒泡排序，依序篩選出第N大的數字並顯示Text1中

For i = 0 To 11

For j = i 1 To 11

If Matrix(j, 1) > Matrix(i, 1) Then

sngTemp = Matrix(i, 1)

Matrix(i, 1) = Matrix(j, 1)

Matrix(j, 1) = sngTemp

sngTemp = Matrix(i, 0)

Matrix(i, 0) = Matrix(j, 0)

Matrix(j, 0) = sngTemp

End If

Next

Text1.Text = Text1.Text & vbCrLf & Matrix(i, 0)

Next

EXCEL VBA巨集語言排序問題附有圖片

問題1

Private Sub CommandButton1_Click()

Dim i, j As Long

Sheets(＂sheet2＂).Cells.ClearContents

Sheets(＂sheet1＂).Cells.Copy

Sheets(＂sheet2＂).Select

Sheets(＂sheet2＂).Range(＂A1＂).Select

ActiveSheet.Paste

i = 3

Do While Sheets(＂sheet2＂).Range(＂A＂ & i).Value ＂＂

For j = 2 To i - 1

If Sheets(＂sheet2＂).Range(＂A＂ & i).Value > Sheets(＂sheet2＂).Range(＂A＂ & j).Value Then

Else

Sheets(＂sheet2＂).Rows(i & ＂:＂ & i).Cut

Sheets(＂sheet2＂).Rows(j & ＂:＂ & j).Insert Shift:=xlDown

Exit For

End If

Next j

i = i 1

Loop

End Sub

問題2

Private Sub CommandButton1_Click()

Dim i, j As Long

Sheets(＂sheet2＂).Cells.ClearContents

Sheets(＂sheet1＂).Cells.Copy

Sheets(＂sheet2＂).Select

Sheets(＂sheet2＂).Range(＂A1＂).Select

ActiveSheet.Paste

i = 3

Do While Sheets(＂sheet2＂).Range(＂A＂ & i).Value ＂＂

For j = 2 To i - 1

If Sheets(＂sheet2＂).Range(＂C＂ & i).Value & Sheets(＂sheet2＂).Range(＂D＂ & i).Value > Sheets(＂sheet2＂).Range(＂ C＂ & j).Value & Sheets(＂sheet2＂).Range(＂D＂ & j).Value Then

Else

Sheets(＂sheet2＂).Rows(i & ＂:＂ & i).Cut

Sheets(＂sheet2＂).Rows(j & ＂:＂ & j).Insert Shift:=xlDown

Exit For

End If

Next j

i = i 1

Loop

End Sub

以上是我的Excel包含三列數據的詳細內容。更多資訊請關注PHP中文網其他相關文章！