|
发表于 2013-8-22 23:56
|
显示全部楼层
本楼为最佳答案
- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim lMoney As Long
- If Target.Count > 1 Then Exit Sub
- If Target.Column <> 2 Then Exit Sub
- If Len(Target.Value) = 0 Then Exit Sub
- If Not VBA.IsNumeric(Target.Value) Then Exit Sub
-
- lMoney = Target.Value
- Application.EnableEvents = False
- Application.ScreenUpdating = False
- lMoney = lMoney
- Target.Offset(0, 1) = Int((lMoney / 100)) '计算100元
- Target.Offset(0, 2) = Int((lMoney - Target.Offset(0, 1) * 100) / 50) '计算50元
- Target.Offset(0, 3) = Int((lMoney - Target.Offset(0, 1) * 100 - Target.Offset(0, 2) * 50) / 20) '计算20元
- Target.Offset(0, 4) = Int((lMoney - Target.Offset(0, 1) * 100 - Target.Offset(0, 2) * 50 - Target.Offset(0, 3) * 20) / 10) '计算10元
- Target.Offset(0, 5) = Int((lMoney - Target.Offset(0, 1) * 100 - Target.Offset(0, 2) * 50 - Target.Offset(0, 3) * 20 - Target.Offset(0, 4) * 10) / 5) '计算5元
- Target.Offset(0, 6) = Int((lMoney - Target.Offset(0, 1) * 100 - Target.Offset(0, 2) * 50 - Target.Offset(0, 3) * 20 - Target.Offset(0, 4) * 10 - Target.Offset(0, 5) * 5) / 2) '计算2元
- Target.Offset(0, 7) = Int((lMoney - Target.Offset(0, 1) * 100 - Target.Offset(0, 2) * 50 - Target.Offset(0, 3) * 20 - Target.Offset(0, 4) * 10 - Target.Offset(0, 5) * 5 - Target.Offset(0, 6) * 2)) '计算1元
- Application.ScreenUpdating = True
- Application.EnableEvents = True
- End Sub
复制代码 |
|