本帖最后由 我行我速2008 于 2022-8-24 12:38 编辑
简单写了下,可能有误!文件地址请自行修改。
Sub 拷贝()
Dim d As Object
Dim Wb As Workbook
Dim Sh As Worksheet
Dim Ar, Br, R, f, X, K
Set d = CreateObject("scripting.dictionary")
Set Sh = ActiveSheet
f = Dir(ThisWorkbook.Path & "\PKG220616.xlsx")
If f = "" Then MsgBox "【PKG220616】文件不存在": Exit Sub
Set Wb = Workbooks.Open(ThisWorkbook.Path & "\" & f)
With Wb.Worksheets("PKG")
Ar = .Range("A1:S" & .Cells(Rows.Count, 2).End(xlUp).Row)
For R = 1 To UBound(Ar)
d(Ar(R, 2) & "-" & Ar(R, 3)) = R
Next R
End With
With Sh
Br = Sh.[a1].CurrentRegion
For X = 4 To UBound(Br)
If d.exists(Br(X, 2) & "-" & Br(X, 4)) Then
K = d(Br(X, 2) & "-" & Br(X, 4))
Br(X, 9) = Ar(K, 4): Br(X, 10) = Ar(K, 5): Br(X, 11) = Ar(K, 6)
If Br(X, 5) < Ar(K, 7) Then Br(X, 12) = Br(X, 5) Else Br(X, 12) = Ar(K, 7)
Br(X, 13) = Int(Br(X, 5) / Ar(K, 9))
Br(X, 14) = Application.RoundUp(Br(X, 5) / Ar(K, 9), 0)
If Br(X, 5) < Ar(K, 9) Then Br(X, 15) = Br(X, 5) Else Br(X, 15) = Ar(K, 9)
Br(X, 16) = Int(Br(X, 5) / Ar(K, 9))
End If
Next X
End With
Wb.Close True
Sh.[a1].CurrentRegion = Br
Set Wb = Nothing
Set Sh = Nothing
End Sub