|
- Sub lqxs()
- Dim Arr, myPath$, myName$, Arr1, Myr&, d, cs$, d1, k, t
- Dim nm$, Brr, y&, m&, dz, d2
- Set d = CreateObject("Scripting.Dictionary")
- Set d1 = CreateObject("Scripting.Dictionary")
- Set dz = CreateObject("Scripting.Dictionary")
- Set d2 = CreateObject("Scripting.Dictionary")
- Application.ScreenUpdating = False
- Sheet1.Activate
- cs = "MPS1"
- myPath = ThisWorkbook.Path & ""
- myName = cs & ".xls"
- With GetObject(myPath & myName)
- Arr1 = .Sheets(1).Range("A1").CurrentRegion
- For i = 2 To UBound(Arr1)
- d(Arr1(i, 1)) = d(Arr1(i, 1)) & i & ","
- Next
- For i = 3 To UBound(Arr1, 2)
- dz(Arr1(1, i)) = i
- Next
- .Close False
- End With
- k = d.keys: t = d.items
- myPath = ThisWorkbook.Path & "\BOM"
- myName = Dir(myPath & "*.xls")
- Do While myName <> ""
- nm = Split(myName, ".")(0)
- If d.exists(nm) Then
- With GetObject(myPath & myName)
- Arr = .Sheets(1).Range("A1").CurrentRegion
- For i = 2 To UBound(Arr)
- d1(Arr(i, 1) & "|" & nm) = Arr(i, 4)
- x = Arr(i, 1) & "," & Arr(i, 2) & "," & Arr(i, 3)
- d2(x) = ""
- Next
- .Close False
- End With
- End If
- myName = Dir
- Loop
- Cells.ClearContents
- [a1].Resize(1, 3) = Array("Part No", "Description", "MU")
- [a2].Resize(d2.Count) = Application.Transpose(d2.keys)
- Application.DisplayAlerts = False
- [a2].Resize(d2.Count).TextToColumns DataType:=xlDelimited, Comma:=True
- Range("d1").Resize(1, dz.Count) = dz.keys
- Brr = [a1].CurrentRegion
- For i = 2 To UBound(Brr)
- For j = 4 To UBound(Brr, 2)
- For y = 2 To UBound(Arr1)
- If d1.exists(Brr(i, 1) & "|" & Arr1(y, 1)) Then
- Brr(i, j) = Brr(i, j) + d1(Brr(i, 1) & "|" & Arr1(y, 1)) * Arr1(y, j - 1)
- End If
- Next
- Next
- Next
- [a1].CurrentRegion = Brr
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|