[已解决]自动分段计算收费

心灵有约

分段收费计算.rar (10.53 KB, 下载次数: 27)

2021-11-16 15:45 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

cutecpu

2021-11-16 16:17

Sub demo()
   Price = [e11]
   If Price > [b10] Then [e11] = "超出计算范围": Exit Sub
   [f4:f10].ClearContents: a = [a4:f10]
   For i = 1 To UBound(a)
      If Price <= a(i, 1) Then Exit For
      a(i, 6) = (Price - a(i, 1) - Application.Max(0, Price - a(i, 2))) * a(i, 3)
   Next
   [a4].Resize(UBound(a), 6) = a
End Sub


祝順心，南無阿彌陀佛！

跳转到最佳答案楼层

cutecpu

Sub demo()
   Price = [e11]
   If Price > [b10] Then [e11] = "超出计算范围": Exit Sub
   [f4:f10].ClearContents: a = [a4:f10]
   For i = 1 To UBound(a)
      If Price <= a(i, 1) Then Exit For
      a(i, 6) = (Price - a(i, 1) - Application.Max(0, Price - a(i, 2))) * a(i, 3)
   Next
   [a4].Resize(UBound(a), 6) = a
End Sub


祝順心，南無阿彌陀佛！

心灵有约

Private Sub CommandButton1_Click()
  Price = [e11]
  
   If Price > [b10] Then [e11] = "超出计算范围"
   Range("F3:F11").Select
    Selection.ClearContents
    [e11].Select: Exit Sub
   
    If Price <= [b3] Then [f3] = [c3]: Exit Sub
   
'    If Price <= [b10] Then
   [f4:f10].ClearContents: a = [a4:f10]
   For i = 1 To UBound(a)
      If Price <= a(i, 1) Then Exit For
      a(i, 6) = (Price - a(i, 1) - Application.Max(0, Price - a(i, 2))) * a(i, 3)
   Next
   [a4].Resize(UBound(a), 6) = a
   
   [f3] = 0.3
   Range("F11").Select
    Selection.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
'    End If
End Sub
改为上面的语句后，当预估金额<=b3时，运行语句则没有反应了。为什么？

cutecpu

心灵有约 发表于 2021-11-17 21:54
Private Sub CommandButton1_Click()
  Price = [e11]
  

拿掉紅色部份  If Price <= [b3] Then [f3] = [c3]: Exit Sub

心灵有约

cutecpu 发表于 2021-11-17 22:49
拿掉紅色部份  If Price

谢谢

心灵有约

cutecpu 发表于 2021-11-16 16:17
Sub demo()
   Price = [e11]
   If Price >  Then [e11] = "超出计算范围": Exit Sub

分段收费计算.rar (15.58 KB, 下载次数: 0)

2021-12-16 18:00 上传

点击文件名下载附件
现在，又增加了3行(详见附件分段收费计算（新)表)，其中B13无限定高限金额，该段金额按0.02%计算。如果E14>100000,则正常计算，否则，D13为空。烦请修改一下现有的代码。