|
本帖最后由 ykcbf1100 于 2024-4-25 11:10 编辑
参与一下。。。
- Sub ykcbf() '//2024.4.25
- Set d = CreateObject("Scripting.Dictionary")
- Set sh = ThisWorkbook.Sheets("寄件运单费")
- bt = 2
- For Each sht In Sheets
- If sht.Name <> sh.Name Then
- With sht
- fn = .Name
- r = .Cells(Rows.Count, 1).End(3).Row
- arr = .[a1].Resize(r, 13)
- End With
- For i = bt + 1 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- tl = IIf(j = 13, 11, Val(arr(2, j)))
- s = fn & "|" & arr(i, 1) & "|" & tl
- d(s) = Array(arr(i, j), arr(i, j - 1))
- Next
- Next
- End If
- Next
- With sh
- r = .Cells(Rows.Count, 1).End(3).Row
- arr = .[a2].Resize(r - 1, 13)
- For i = 2 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- tt = arr(i, 9)
- t1 = Fix(tt): t2 = tt - t1
- If t2 >= 0.5 Then
- t3 = t1 + 1
- Else
- t3 = IIf(t2 > 0, t1 + 0.5, t1)
- End If
- arr(i, 10) = t3
- s = arr(i, 5) & "|" & arr(i, 13) & "|" & IIf(t3 > 10, 11, t3)
- If d.exists(s) Then
- If arr(i, 10) > 10 Then
- arr(i, 11) = d(s)(0) * (arr(i, 10) - 10) + d(s)(1)
- Else
- arr(i, 11) = d(s)(0) * arr(i, 10)
- End If
- End If
- Next
- Next
- .[a2].Resize(r - 1, 13) = arr
- End With
- Set d = Nothing
- MsgBox "OK!"
- End Sub
复制代码
|
|