|
vhanhong 发表于 2014-12-1 17:02
用VBA代替公式,单元格内容改变自动运行,谢谢
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arr(1 To 5, 1 To 2), i
x = Cells(Target.Row, 5)
arr(1, 1) = "怡纯,350ml,1×24": arr(1, 2) = 84
arr(2, 1) = "怡纯,555ml,1×12[彩膜]": arr(2, 2) = 110
arr(3, 1) = "怡纯,555ml,1×24": arr(3, 2) = 70
arr(4, 1) = "怡纯,1555ml,1×12": arr(4, 2) = 44
arr(5, 1) = "怡纯,4500ml,1×4": arr(5, 2) = 36
If Target.Row > 3 And (Target.Column = 4 Or Target.Column = 5) Then
For i = 1 To UBound(arr)
If x <> "" Then
If Cells(Target.Row, 4) = arr(i, 1) Then
If x \ arr(i, 2) = 0 Then
Cells(Target.Row, 6) = x Mod arr(i, 2) & "箱"
Else
Cells(Target.Row, 6) = x \ arr(i, 2) & "托" & x Mod arr(i, 2) & "箱"
End If
Exit For
End If
End If
Next
End If
End Sub
在第4,5列,单元格触发事件
|
|