標題: [發問] 根據條件套用不同良率計算 [打印本頁]

---

作者: adam2010    時間: 2023-10-1 18:20     標題: 根據條件套用不同良率計算

請問論壇的各位大大~
[attach]36853[/attach]
WIP公作表中的D欄位根據條件套用不同良率計算
[attach]36854[/attach]
判斷來源
[attach]36855[/attach]
特殊料號後續還會陸續增修，如何將D欄公式寫成巨集以免每次增修後都要更改公式

---

作者: adam2010    時間: 2023-10-4 22:32

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

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

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)