|
楼主 |
发表于 2024-4-28 16:22
|
显示全部楼层
本帖最后由 jjc_1212 于 2024-4-28 16:58 编辑
老师,您的意思是增加您发的这段吗?尝试了一下,结果不对,麻烦老师看看怎么调整
Sub AwTest()
Dim i&, eRow&, k&, sName$, Arr, d As Object, Dts As Object, t
Set d = CreateObject("Scripting.Dictionary")
Set Dts = CreateObject("Scripting.Dictionary")
For Each x In Array("导入模板", "A", "B", "C", "D", "整托发货", "异常")
With Sheets(x)
.Range("A1").CurrentRegion.Offset(1) = ""
End With
Next
With Sheets("标包")
Arr = .Range("A1").CurrentRegion
For i = 2 To UBound(Arr)
d(UCase(CStr(Arr(i, 1))) & "TEST") = Array(Arr(i, 2))
Next
End With
With Sheets("分区数据")
Arr = .Range("A1").CurrentRegion
For i = 2 To UBound(Arr)
d(UCase(CStr(Arr(i, 1)))) = Array(Arr(i, 2))
Next
End With
With Sheets("整托数据")
Arr = .Range("A1").CurrentRegion
For i = 2 To UBound(Arr)
Dts(UCase(CStr(Arr(i, 1)))) = Arr(i, 2)
Next
End With
With Sheets("拉动数据")
Arr = .Range("A1").CurrentRegion
For i = 2 To UBound(Arr)
If Dts.exists(UCase(CStr(Arr(i, 3)))) Then
sName = "整托发货"
Else
If d.exists(UCase(CStr(Arr(i, 3)))) And d.exists(UCase(CStr(Arr(i, 3))) & "TEST") Then
sName = d(UCase(CStr(Arr(i, 3))))(0)
Else
sName = "异常"
End If
End If
With Sheets(sName)
eRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(eRow, 1) = Arr(i, 1)
.Cells(eRow, 3) = Arr(i, 2)
.Cells(eRow, 4) = Arr(i, 3)
.Cells(eRow, 6) = Arr(i, 5)
If sName = "异常" Then
If Not d.exists(UCase(CStr(Arr(i, 3))) & "TEST") Then .Cells(eRow, 5) = "缺料" Else .Cells(eRow, 5) = "无分区信息"
ElseIf sName = "整托发货" Then
k = Dts(UCase(CStr(Arr(i, 3))))
.Cells(eRow, 5) = Application.Ceiling(Arr(i, 4) / d(UCase(CStr(Arr(i, 3))) & "TEST")(0) / k, 1) * k
Else
mn = d(CStr(Arr(i, 3)) & "TEST")
x = UCase(CStr(Arr(i, 3)))
.Cells(eRow, 5) = Application.Round(Arr(i, 4) / d(UCase(CStr(Arr(i, 3))) & "TEST")(0), 0)
End If
End With
Next
End With
End Sub
|
|