|
- Sub main()
- Application.ScreenUpdating = False
- Sheets("表1").Range("c3:k61") = "" '清空单元格
- With Sheets("Sheet1")
- icount = .[a1].CurrentRegion.Rows.Count
- For a = 5 To icount '职工人数
- b = .Cells(a, 20).Value '任职年限
- c = .Cells(a, 14).Value '工作年限
- gw = .Cells(a, 18)
- Call 汇总(gw, b, c) '根据岗位、任职年限、工作年限找到对应的单元格位置后+1
- Next
- End With
- Application.ScreenUpdating = True
- End Sub
- Sub 汇总(gw, b, c) '根据岗位、任职年限、工作年限汇总
- With Sheets("表1")
- Set xrng = .[a:a].Find(gw, lookat:=xlWhole)
- If Not xrng Is Nothing Then
- r = xrng.Row '根据工作岗位找到对应的行
- r1 = IIf(b < 6, r, IIf(b < 11, r + 1, IIf(b < 16, r + 2, r + 3))) '根据任职年限确定要填充的行
- c1 = IIf(c = 0, 3, Int((c - 0.1) / 5) + 3) '根据工作年限确定要填充的列
- .Cells(r1, c1) = Val(.Cells(r1, c1)) + 1
- .Cells(75, c1) = Val(.Cells(75, c1)) + 1
- End If
- End With
- End Sub
复制代码 |
|