计算公式

nowtoo　发表于：2018-01-13 16:21:29 　

下面测试是坛中一位版主设计的，str3是我加上去的，目的要获得计算公式，但不正确，请高手协助修改一下，非常感谢！！！

Sub test()
    Dim str    As String
    Dim str1   As String
    Dim str2   As String
    Dim str3   As String
    Dim i
    Dim a
    Dim b
    Dim r
    str = InputBox("请输入分段信息,用逗号分隔", , "100,1000")
    str1 = InputBox("请输入计算比例,用逗号分隔", , "0.5,0.6")
    str2 = InputBox("请输入需要计算的数值", , "2500")
    a = Split(str, ",")
    b = Split(str1, ",")
    For i = 0 To UBound(a)
        If i < UBound(a) Then
            r = r + (CInt(a(i + 1)) - CInt(a(i))) * b(i)
            str3 = "(" & CInt(a(i + 1)) & "-" & CInt(a(i)) & ")*" & FormatPercent(b(i), 2) & "=" & r
        Else
            r = r + (CInt(str2) - CInt(a(i))) * b(i)
            str3 = "(" & CInt(str2) & "-" & CInt(a(i)) & ")*" & FormatPercent(b(i), 2) & "=" & r
        End If
    Next
    MsgBox str3
    MsgBox UBound(a)
End Sub

Top

sanny 发表于：2018-01-16 23:10:19

不知道是不是你要的.

Sub test()
Dim str As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim i
Dim a
Dim b
Dim r
str = InputBox("请输入分段信息,用逗号分隔", , "100,1000,1500")
str1 = InputBox("请输入计算比例,用逗号分隔", , "0.5,0.6,0.7")
str2 = InputBox("请输入需要计算的数值", , "2500")
a = Split(str, ",")
b = Split(str1, ",")
For i = 0 To UBound(a)
If i < UBound(a) Then
r = r + (CInt(a(i + 1)) - CInt(a(i))) * b(i)

If Len(str3) < 1 Then
str3 = "(" & CInt(a(i + 1)) & "-" & CInt(a(i)) & ")*" & FormatPercent(b(i), 2)
Else
str3 = str3 & "+" & "(" & CInt(a(i + 1)) & "-" & CInt(a(i)) & ")*" & FormatPercent(b(i), 2)
End If
Else
r = r + (CInt(str2) - CInt(a(i))) * b(i)
str3 = str3 & "+" & "(" & CInt(str2) & "-" & CInt(a(i)) & ")*" & FormatPercent(b(i), 2) & "=" & r
End If
Next
MsgBox str3
MsgBox UBound(a)
End Sub

Top

nowtoo 发表于：2018-01-17 19:59:58

非常感谢sanny的热心帮助！

分段信息：100，1000，5000，10000，30000

计算比例：0.5，0.6，0.7，0.8，0.9

需要计算的金额：40000

出错

Top

sanny 发表于：2018-01-17 23:02:46

这个是因为数值范围的问题,integer型数字范围没有那么大.改为clng再试

Sub test()

Dim str As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim i
Dim a
Dim b
Dim r
str = InputBox("请输入分段信息,用逗号分隔", , "100,1000,5000,10000,30000")
str1 = InputBox("请输入计算比例,用逗号分隔", , "0.5,0.6,0.7,0.8,0.9")
str2 = InputBox("请输入需要计算的数值", , "40000")
a = Split(str, ",")
b = Split(str1, ",")
For i = 0 To UBound(a)
If i < UBound(a) Then
r = r + (CLng(a(i + 1)) - CLng(a(i))) * b(i)

If Len(str3) < 1 Then
str3 = "(" & CLng(a(i + 1)) & "-" & CLng(a(i)) & ")*" & FormatPercent(b(i), 2)
Else
str3 = str3 & "+" & "(" & CLng(a(i + 1)) & "-" & CLng(a(i)) & ")*" & FormatPercent(b(i), 2)
End If
Else
r = r + (CLng(str2) - CLng(a(i))) * b(i)
str3 = str3 & "+" & "(" & CLng(str2) & "-" & CLng(a(i)) & ")*" & FormatPercent(b(i), 2) & "=" & r
End If
Next
MsgBox str3
MsgBox UBound(a)
End Sub

Top

总记录：3篇页次：1/1 9 1 :

Access交流中心

[5分]计算公式