本帖最后由 龙送农 于 2016-6-29 13:17 编辑
VBA调年龄或工龄名单: 1、年龄段名单:条件1是单元格F2(单位),条件2是岁段单元格M2至O2。 2、工龄段名单:条件1是单元格F2(单位),条件2是岁段单元格V2至X2。 分别选择或录入上述单元格信息后,自动调出本表名单信息。
代码自己替换下! - Dim y%
- Sub 按工龄()
- y = 17
- If [V2] = "" Or [X2] = "" Then Exit Sub
- Call xx([V2], [X2])
- End Sub
- Sub 按年龄()
- y = 15
- If [M2] = "" Or [O2] = "" Then Exit Sub
- Call xx([M2], [O2])
- End Sub
- Sub xx(u As Integer, v As Integer)
- Dim arr, i&, j&, n&, k&
- n = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row
- arr = Sheet1.Range("A5:BG" & n)
- Application.ScreenUpdating = False
- k = 4
- With Sheet3
- .Range("a5:ag5000").Delete
- For i = 1 To n - 4
- If .[f2] = "全部" Then
- If arr(i, y) >= u And arr(i, y) <= v Then
- k = k + 1
- .Cells(k, 1) = k - 4
- .Cells(k, 2) = arr(i, 3)
- .Cells(k, 3) = arr(i, 8) '机构,自己修改
- .Cells(k, 4) = arr(i, 5)
- .Cells(k, 5) = arr(i, 6)
- .Cells(k, 6) = arr(i, 7)
- For j = 7 To 10
- .Cells(k, j) = arr(i, j + 2)
- Next
- For j = 11 To 24
- .Cells(k, j) = arr(i, j + 3)
- Next
- For j = 25 To 32
- .Cells(k, j) = arr(i, j + 6)
- Next
- .Cells(k, 33) = arr(i, 59)
- End If
- Else
- If arr(i, y) >= u And arr(i, y) <= v And arr(i, 4) = .[f2] Then
- k = k + 1
- .Cells(k, 1) = k - 4
- .Cells(k, 2) = arr(i, 3)
- .Cells(k, 3) = arr(i, 8) '机构,自己修改
- .Cells(k, 4) = arr(i, 5)
- .Cells(k, 5) = arr(i, 6)
- .Cells(k, 6) = arr(i, 7)
- For j = 7 To 10
- .Cells(k, j) = arr(i, j + 2)
- Next
- For j = 11 To 24
- .Cells(k, j) = arr(i, j + 3)
- Next
- For j = 25 To 32
- .Cells(k, j) = arr(i, j + 6)
- Next
- .Cells(k, 33) = arr(i, 59)
- End If
- End If
-
- Next
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码
|