1

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
Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
Nini
  • 13
  • 3
  • Why do you have `InvestProg` out of the sudden? Is that actually `RiskPro`? – Raymond Wu Aug 25 '21 at 09:25
  • `Select Case Value & RiskPro` will stick those two figures together and treat them as one value - so your checking to see if, for example, _15000000Foreign Assertive_ = "Foreign Assertive" which it doesn't. – Darren Bartrup-Cook Aug 25 '21 at 09:42

2 Answers2

2

  1. Please insert Option Explicit at the top of the module to help you enforce declaring all variables, it also help you to catch typo, like RiskProgLR

  2. Multiple conditions for Select statement has a different way of doing it, look at this answer for example

  3. Don't name your variable with the same name as an existing object property. e.g. Value

  4. You are advised to fully qualify your range (e.g. Range("C1").Offset(0, 1) should be something like ThisWorkBook.Sheets("Sheet1").Range("C1").OffSet(0,1), or make a variable as shown below and Set it so that you can just refer to the variable), not doing so will cause VBA to assume that you are referring to the ActiveSheet which might not be what you want.

Try this modified code, I assume InvestProg is actually RiskPro:

Sub FeeTest()

    Dim RiskProLR As Long, x As Long, feeValue As Long
    Dim feeRng As Range
    Dim RiskPro As String
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Set feeRng = ws.Range("C1").Offset(0, 1)
    
    feeRng.Value = "Fee"
    
    RiskProLR = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
    For x = 2 To RiskProLR
    
        feeValue = ws.Range("C" & x).Value
        RiskPro = ws.Range("B" & x).Value
        
        Dim feeRate As String
        feeRate = vbNullString
        
        Select Case RiskPro
            Case "Foreign Assertive", "Foreign Balanced", "Local Assertive", "Local Balanced"
                Select Case True
                    Case feeValue <= 15000000
                        feeRate = "0.8%"
                    Case (feeValue > 15000000 And feeValue <= 30000000)
                        feeRate = "0.6%"
                    Case (feeValue > 30000000 And feeValue <= 60000000)
                        feeRate = "0.4%"
                    Case feeValue > 60000000
                        feeRate = "0.2%"
                End Select
            Case "Local Fixed Income", "Foreign Fixed Income"
                Select Case True
                    Case feeValue <= 15000000
                        feeRate = "0.6%"
                    Case (feeValue > 15000000 And feeValue <= 30000000)
                        feeRate = "0.4%"
                    Case feeValue > 30000000
                        feeRate = "0.2%"
                End Select
        End Select
        
        If feeRate <> vbNullString Then ws.Range("D" & x).Value = feeRate
    Next x
End Sub
Raymond Wu
  • 3,357
  • 2
  • 7
  • 20
1

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.

Jonathan Willcock
  • 5,012
  • 3
  • 20
  • 31