vba筛选排序
Option Explicit
Sub 天地齐寿⽇⽉同庚()
Dim i%, b As Workbook, fname$, j2%, j3%, j4%, j5%, k%
fname = "天地齐寿，⽇⽉同庚.xls"
Set b = Workbooks.Open("C:\Users\admin\Desktop\" & fname)
fname = "天地齐寿"
b.SaveAs Filename:="C:\Users\admin\Desktop\" & fname, FileFormat:=xlWorkbookNormal Set b = Workbooks.Open("C:\Users\admin\Desktop\" & fname & ".xls")
'先对第5列进⾏降序排序
'定义⼀个range().sort，然后定义Key1，order1
'range()定义整个数据范围，Key1⽤于定义哪⼀列，order1定义升序Ascend或者降序Descend Worksheets(1).Range("A:E").Sort _
Key1:=Worksheets(1).Cells(1, 5), order1:=xlDescending
'添加4张⼦表，并把C列剪切到B列前⾯
Worksheets.Add after:=Worksheets(1), count:=4
Worksheets(2).Name = "100~199"
Worksheets(3).Name = "200~299"
Worksheets(4).Name = "300～399"
Worksheets(5).Name = "400以上"
Worksheets(1).Columns("C:C").Cut
Worksheets(1).Columns("B:B").Insert Shift:=xlToRight
i = 2
j2 = 2: j3 = 2: j4 = 2: j5 = 2
'判断取值范围并把数据复制到其他相对应的⼦表
Do While Worksheets(1).Cells(i, 5) <> ""
If Worksheets(1).Cells(i, 5) >= 100 And Worksheets(1).Cells(i, 5) <= 199 Then
Worksheets(2).Cells(j2, 1) = Worksheets(1).Cells(i, 1)
Worksheets(2).Cells(j2, 2) = Worksheets(1).Cells(i, 2)
j2 = j2 + 1
ElseIf Worksheets(1).Cells(i, 5) >= 200 And Worksheets(1).Cells(i, 5) <= 299 Then
Worksheets(3).Cells(j3, 1) = Worksheets(1).Cells(i, 1)
Worksheets(3).Cells(j3, 2) = Worksheets(1).Cells(i, 2)
j3 = j3 + 1
ElseIf Worksheets(1).Cells(i, 5) >= 300 And Worksheets(1).Cells(i, 5) <= 399 Then
Worksheets(4).Cells(j4, 1) = Worksheets(1).Cells(i, 1)
Worksheets(4).Cells(j4, 2) = Worksheets(1).Cells(i, 2)
vba排序函数sort用法j4 = j4 + 1
ElseIf Worksheets(1).Cells(i, 5) >= 400 Then
Worksheets(5).Cells(j5, 1) = Worksheets(1).Cells(i, 1)
Worksheets(5).Cells(j5, 2) = Worksheets(1).Cells(i, 2)
j5 = j5 + 1
End If
i = i + 1
Loop
b.Close
End Sub

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。