[發問] 根據條件套用不同良率計算

adam2010

請問論壇的各位大大~
良率後數量 20231001.rar (91.89 KB)

下載次數: 3

2023-10-1 18:16

WIP公作表中的D欄位根據條件套用不同良率計算

下載 (254.24 KB)

2023-10-1 18:16

判斷來源

下載 (87.51 KB)

2023-10-1 18:16

特殊料號後續還會陸續增修，如何將D欄公式寫成巨集以免每次增修後都要更改公式

adam2010

感謝Andy2483在一般區的協助，已成功解決給大家參考~
將說明標題修改

下載 (5.13 KB)

2023-10-4 22:30

公式簡化 20231002-4.rar (114.31 KB)

下載次數: 3

2023-10-4 22:30

Sub TEST()
Dim Brr, Crr, Z, i&, j%, V%, V7%
Set Z = CreateObject("Scripting.Dictionary")
With Sheets("說明"): Brr = Range(.Cells(.UsedRange.Rows.Count, "A"), .[IV1].End(xlToLeft)): End With
For j = 1 To UBound(Brr, 2)
   If Left(Trim(Brr(1, j)), 6) <> "" Then Z(Left(Trim(Brr(1, j)), 6)) = j
Next
Crr = Range([WIP!O1], [WIP!A65536].End(3))
For i = 2 To UBound(Crr)
   V = Val(Crr(i, 15)): V7 = Val(Crr(i, 7)): Crr(i - 1, 1) = ""
   If Z.Exists(Left(Trim(Crr(i, 1)), 6)) <> Empty Then
      Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Left(Trim(Crr(i, 1)), 6))) * V, 0)
      ElseIf Right(Left(Crr(i, 1), 7), 1) = "W" Then
         Crr(i - 1, 1) = Round(Brr(V7 + 2, Z("W")) * V, 0)
      Else
            Crr(i - 1, 1) = Round(Brr(V7 + 2, Z(Left(Crr(i, 2), 1))) * V, 0)
         
   End If
Next
[WIP!D2].Resize(UBound(Crr) - 1, 1) = Crr
Set Z = Nothing: Erase Brr, Crr
End Sub