|
- Sub SumFile()
- Dim arr()
- Dim i As Long
- Dim arrCol, arrResult()
- Dim strGroup As String * 1, strCompany As String * 6
- Dim strPath As String, strFile As String
- Dim lRow As Long, lCol As Long
- Dim objDic As Object
-
- Application.ScreenUpdating = False
- arrCol = Array(2, 10, 16, 21)
- i = Cells(Rows.Count, 1).End(xlUp).Row
- arr = Range("a6:b" & i).Value
- Set objDic = CreateObject("scripting.dictionary")
- For i = LBound(arr) To UBound(arr)
- objDic(arr(i, 1)) = i
- Next
- ReDim arrResult(1 To UBound(arr), 1 To UBound(arrCol) + 1)
- For i = 65 To 68
- objDic(Chr(i)) = i - 64
- Next
- strPath = ThisWorkbook.Path & Application.PathSeparator
- strFile = Dir(strPath & "*.xls")
- Do While Len(strFile)
- If strFile <> ThisWorkbook.Name Then
- strGroup = Left(strFile, 1)
- strCompany = Mid(strFile, 2, 6)
- If objDic.exists(strGroup) And objDic.exists(strCompany) Then
- lCol = objDic(strGroup)
- lRow = objDic(strCompany)
- arrResult(lRow, lCol) = arrResult(lRow, lCol) + 1
- Debug.Print strFile & ":" & strGroup & ":" & strCompany & ":" & lRow & ":" & lCol
- End If
- End If
- strFile = Dir
- Loop
- For i = LBound(arrCol) To UBound(arrCol)
- Cells(6, arrCol(i)).Resize(UBound(arrResult)).Value = WorksheetFunction.Index(arrResult, 0, i + 1)
- Next
- Set objDic = noting
- Application.ScreenUpdating = True
- MsgBox "汇总完成", vbInformation + vbOKOnly
- End Sub
复制代码 |
|