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.