我的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中文网其他相关文章！
