|
在原代码的基础上小改了一下。- Sub grf()
- Dim d As Object, arr, fl$, tp$, wk, Wb As Workbook, Sh As Worksheet, iRow&, a%, i&
- Set Sh = ActiveSheet
- Set d = CreateObject("scripting.dictionary")
- arr = Sh.Range("a3:o" & Sh.[c65536].End(3).Row)
- For i = 2 To UBound(arr) '已有项目进字典
- If arr(i, 3) = "" Then arr(i, 3) = arr(i - 1, 3)
- xm = arr(i, 3)
- If Len(xm) > 0 Then
- d(xm) = ""
- d(xm & arr(i, 4)) = arr(i, 14) '项目+模板的完成情况
- End If
- Next
-
- tp = ThisWorkbook.Path & ""
- fl = Dir(tp & "*.xls*")
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Do While Len(fl)
- If fl <> ThisWorkbook.Name Then
- Set wk = Workbooks.Open(tp & fl)
- With wk.Sheets(1)
- xm = .[b4] '项目
- If Not d.exists(xm) Then '项目不存在,新增
- iRow = Sh.[d65536].End(3).Row + 1
- Sh.Cells(iRow, "b") = Application.WorksheetFunction.Max(Sh.Range("b:b")) + 1 '序号
- Sh.Cells(iRow, "o") = Sh.Cells(iRow, "b")
- Sh.Cells(iRow, "c") = xm
- Sh.Cells(iRow, "d").Resize(7, 1) = .[a17].Resize(7, 1).Value
- Sh.Cells(iRow, "e").Resize(7, 1) = .[F17].Resize(7, 1).Value
- Sh.Cells(iRow, "f").Resize(7, 1) = .[g17].Resize(7, 1).Value
- Sh.Cells(iRow, "g") = .[f4]
- Sh.Cells(iRow, "h") = .[h4]
- Sh.Cells(iRow, "i") = .[j6] & ":" & [k6]
- Sh.Cells(iRow, "j") = .[c8]
- Sh.Cells(iRow, "k") = .[b30]
- Sh.Cells(iRow, "N") = .[m4]
- Else '项目存在,填充M列
- For i = 17 To 24
- x = xm & .Cells(i, 1)
- If d.exists(x) Then Cells(i, "m") = d(x)
- Next
- End If
- End With
- wk.Close (True)
- End If
- fl = Dir
- Loop
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|