本帖最后由 lsc900707 于 2017-4-11 17:36 编辑
根据输入指定日期,将日期上一月入职人员自动生成入职报表
http://club.excelhome.net/thread-1339187-1-1.html
(出处: ExcelHome技术论坛)
根据楼主要求和一指禅62老师的纠错,代码修改如下(特别鸣谢一指禅62老师)
- <div class="blockcode"><blockquote>Sub lsc()
- Dim brr(1 To 10000, 1 To 14)
- arr = Sheet1.Range("a1:ap" & Sheet1.Cells(Rows.Count, 1).End(3).Row)
- For i = 2 To UBound(arr)
- If Format(arr(i, 5), "yyyymm") = Format(DateAdd("m", -1, Range("N2")), "yyyymm") Then
- k = k + 1
- brr(k, 1) = k: brr(k, 2) = arr(i, 2): brr(k, 3) = arr(i, 3): brr(k, 4) = arr(i, 4)
- brr(k, 5) = arr(i, 5): brr(k, 6) = arr(i, 16): brr(k, 7) = arr(i, 17): brr(k, 8) = arr(i, 18)
- brr(k, 9) = arr(i, 19): brr(k, 10) = arr(i, 20): brr(k, 11) = arr(i, 21): brr(k, 12) = arr(i, 37)
- brr(k, 13) = arr(i, 38): brr(k, 14) = arr(i, 39)
- End If
- Next
- With ActiveSheet
- .[a4:n1000].Clear
- .Columns("m").NumberFormatLocal = "@"
- .[a4].Resize(k, 14) = brr
- End With
- End Sub
复制代码
|