Search code examples
vbacase-statement

Issue with case statement on VBA

I am very new to VBA and I having an issue with a Case Statement I am trying to write.

Overview of what I want the code to do

I need to assign different fee rates based on two criteria: the risk profile and value.

If the risk profile is Foreign Assertive, Foreign Balanced, Local Assertive, Local Balanced, the fees are as per below:

  • less than or equal to 15,000,000 - 0.8%
  • greater than 15,000,000 but less than or equal to 30,000,000 - 0.6%
  • greater than to 30,000,000 but less than or equal to 60,000,000- 0.4%
  • greater than 60,000,000 - 0.2%

And if the risk profile is Local Fixed Income or Foreign Fixed Income, the fees are as per below:

  • less than or equal to 15,000,000 - 0.6%
  • greater than 15,000,000 but less than or equal to 30,000,000 - 0.4%
  • greater than 30,000,000 - 0.2%

Below is an example of my set of data:

Account No Risk Profile Value
2345 Foreign Assertive 5,000,000
2346 Foreign Assertive 25,000,000
2347 Local Assertive 100,000,000
2348 Foreign Balanced 46,000,000
2349 Local Balanced 30,000,000
2350 Foreign Fixed Income 19,000,000
2351 Local Fixed Income 4,000,000
2352 Local Fixed Income 150,000,000

My Expected results is below:

Account No Risk Profile Value Fee
2345 Foreign Assertive 5,000,000 0.80%
2346 Foreign Assertive 25,000,000 0.60%
2347 Local Assertive 100,000,000 0.20%
2348 Foreign Balanced 46,000,000 0.40%
2349 Local Balanced 30,000,000 0.60%
2350 Foreign Fixed Income 19,000,000 0.40%
2351 Local Fixed Income 4,000,000 0.60%
2352 Local Fixed Income 150,000,000 0.20%

Below is what what I wrote and it is not working:

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"

RiskProgLR = Range("B" & Rows.Count).End(xlUp).Row

 For x = 2 To RiskProLR

    Value = Range("C" & x).Value
    RiskPro = Range("B" & x).Value

    Select Case Value & RiskPro
  
        Case Is = RiskPro = "Foreign Assertive", RiskPro = "Local Assertive", RiskPro = "Foreign Balanced", _
        RiskPro = "Local Balanced" & Value <= 15000000
        Range("D" & x).Value = "0.8%"
    
        Case Is = RiskPro = "Foreign Assertive", RiskPro = "Local Assertive", RiskPro = "Foreign Balanced", _
        RiskPro = "Local Balanced" & Value > 15000000 & Value <= 30000000
        Range("D" & x).Value = "0.6%"
    
        Case Is = RiskPro = "Foreign Assertive", RiskPro = "Local Assertive", RiskPro = "Foreign Balanced", _
        RiskPro = "Local Balanced" & Value > 30000000 & Value <= 60000000
        Range("D" & x).Value = "0.4%"
        
        Case Is = RiskPro = "Foreign Assertive", RiskPro = "Local Assertive", RiskPro = "Foreign Balanced", _
        RiskPro = "Local Balanced" & Value > 60000000
        Range("D" & x).Value = "0.2%"
        
        Case Is = InvestProg = "Foreign Fixed Income", InvestProg = "Local Fixed Income" & PortValue <= 15000000
        Range("D" & x).Value = "0.6%"
        
        Case Is = InvestProg = "Foreign Fixed Income", InvestProg = "Local Fixed Income" & Value > 15000000 & Value <= 30000000
        Range("D" & x).Value = "0.4%"
        
        Case Is = InvestProg = "Foreign Fixed Income", InvestProg = "Local Fixed Income" & PortValue > 30000000
        Range("D" & x).Value = "0.2%"
    
        
    End Select

 
Next x


End Sub
Solution

  • I agree with Raymond Wu's general comments so do not repeat them.

    You may find the following code, which does the same a bit simpler:

    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 RiskPro
  
        Case "Foreign Assertive", "Local Assertive", "Foreign Balanced", "Local Balanced"
            Select Case Value
                Case Is <= 15000000
                    Range("D" & x).Value = "0.8%"
                Case 15000000 To 30000000
                    Range("D" & x).Value = "0.6%"
                Case 30000000 To 60000000
                    Range("D" & x).Value = "0.4%"
                Case Else
                    Range("D" & x).Value = "0.2%"
            End Select
            
        Case "Foreign Fixed Income", "Local Fixed Income"
            Select Case Value
                Case Is <= 15000000
                    Range("D" & x).Value = "0.6%"
                Case 15000000 To 30000000
                    Range("D" & x).Value = "0.4%"
                Case Else
                    Range("D" & x).Value = "0.2%"
            End Select
        
    End Select

 
Next x


End Sub

    Using Case x To y for specifying a range is a bit easier to type and is more readable. Notice that it is ok to have the ranges effectively overlapping. VBA will use the first case that fits the criteria, so where for example a value is exactly 30,000,000 it falls in the 15,000,000 To 30,000,000 range, not the 30,000,000 to 60,000,000 one. Order of the case statements is here important.

    Note also the use of Case Else. There is no need to specify the last amount.