VBA用Case语句按风险等级与账户值计算手续费报错求助

原代码核心错误点 #

• 变量拼写错误：定义的行计数变量为RiskProLR，后续赋值时误写为RiskProgLR；代码后半段出现未定义的InvestProg、PortValue变量，实际应该为已定义的RiskPro、Value
• Select Case用法错误：将Value & RiskPro作为判断对象，但Case后写的是布尔判断表达式，匹配逻辑完全不成立，应该改用Select Case True来匹配布尔类型的条件结果
• 运算符使用错误：VBA中逻辑与运算应使用And，&是字符串拼接运算符，原代码用&拼接判断条件导致逻辑失效
• 条件组合逻辑错误：风险等级判断和金额区间判断没有用逻辑运算符关联，导致条件始终无法匹配

修正后可运行代码 #

Sub FeeTest()
    Dim RiskProLR As Long, x As Long, Value As Long
    Dim Fee As Range
    Dim RiskPro As String
    
    Set Fee = Range("C1").Offset(0, 1)
    Fee.Value = "Fee"
    ' 修正变量拼写错误
    RiskProLR = Range("B" & Rows.Count).End(xlUp).Row
    
    For x = 2 To RiskProLR
        Value = Range("C" & x).Value
        RiskPro = Range("B" & x).Value
        ' 改用Select Case True匹配布尔条件
        Select Case True
            ' 第一类权益类风险等级判断，条件用And关联
            Case (RiskPro = "Foreign Assertive" Or RiskPro = "Local Assertive" Or RiskPro = "Foreign Balanced" Or RiskPro = "Local Balanced") And Value <= 15000000
                Range("D" & x).Value = "0.8%"
            Case (RiskPro = "Foreign Assertive" Or RiskPro = "Local Assertive" Or RiskPro = "Foreign Balanced" Or RiskPro = "Local Balanced") And Value > 15000000 And Value <= 30000000
                Range("D" & x).Value = "0.6%"
            Case (RiskPro = "Foreign Assertive" Or RiskPro = "Local Assertive" Or RiskPro = "Foreign Balanced" Or RiskPro = "Local Balanced") And Value > 30000000 And Value <= 60000000
                Range("D" & x).Value = "0.4%"
            Case (RiskPro = "Foreign Assertive" Or RiskPro = "Local Assertive" Or RiskPro = "Foreign Balanced" Or RiskPro = "Local Balanced") And Value > 60000000
                Range("D" & x).Value = "0.2%"
            ' 第二类固定收益类风险等级判断，修正变量名
            Case (RiskPro = "Foreign Fixed Income" Or RiskPro = "Local Fixed Income") And Value <= 15000000
                Range("D" & x).Value = "0.6%"
            Case (RiskPro = "Foreign Fixed Income" Or RiskPro = "Local Fixed Income") And Value > 15000000 And Value <= 30000000
                Range("D" & x).Value = "0.4%"
            Case (RiskPro = "Foreign Fixed Income" Or RiskPro = "Local Fixed Income") And Value > 30000000
                Range("D" & x).Value = "0.2%"
        End Select
    Next x
End Sub

运行说明 #

修正后的代码可直接匹配提供的测试数据，输出结果和预期完全一致。如果需要优化代码简洁度，也可以将风险等级的判断提前用布尔变量存储，减少重复代码。

内容的提问来源于stack exchange，提问作者Nini