|
我帮您在「核算价格」表加了以下代码
这样 D 列有变动时,J 列的成本就会自动运算
也不用按按钮了(您到时再把按钮拿掉)
※同样注意以下红色部份的中文字
Public d As Object
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
If d Is Nothing Then
Set d = CreateObject("Scripting.Dictionary")
a = Sheets(1).Range("b3:i" & Sheets(1).[b3].End(4).Row)
For i = 1 To UBound(a)
Key = a(i, 1)
d(Key & " " & 3) = a(i, 3)
d(Key & " " & 6) = a(i, 6)
d(Key & " " & 7) = a(i, 7)
d(Key & " " & 8) = a(i, 8)
Next
End If
If Target = "" Then Target.Offset(, 6) = "": Exit Sub
cost = 0
Key = Target.Offset(, 3)
h = Target.Offset(, 4)
lv = Left(Target.Offset(, 5), 1)
k = IIf(InStr("二四", lv), "", Mid(Key, 1, 1))
cost = cost + d(k & " " & 3)
k = IIf(InStr("二四", lv), Mid(Key, 1, 1), Mid(Key, 2, 1))
Select Case h
Case "EB", "E": n = 6
Case "A": n = 8
Case Else: n = 7
End Select
cost = cost + d(k & " " & n)
k = IIf(InStr("二四", lv), Mid(Key, 2, 1), Mid(Key, 3, 1))
cost = cost + d(k & " " & 3)
k = IIf(lv = "四", Mid(Key, 3, 1), Mid(Key, 4, 1))
Select Case h
Case "EB", "E": n = 7
Case Else: n = IIf(lv = "七", 7, 8)
End Select
cost = cost + d(k & " " & n)
k = IIf(lv = "四", Mid(Key, 4, 1), Mid(Key, 5, 1))
cost = cost + d(k & " " & 3)
k = Mid(Key, 6, 1)
cost = cost + d(k & " " & IIf(lv = "E", 7, 8))
k = Mid(Key, 7, 1)
cost = cost + d(k & " " & 3)
Target.Offset(, 6) = cost
End Sub
|
|