Select case and if funcitons

Copper Contributor

Hi I'm trying to get a vba module to apply a different value depending on a test selected in a dropdown box, a test score between one or two people, and if two people have done the test, i'd like to give an average result based on their scores.

I made a super convoluted attempt at this, but it's not working and I've hit a block.

How would I go about approaching this?

Thanks heaps for any help.

 

My attempt:

 

Public Function riskprofile(ScoreC1, ScoreC2 As Double)

Dim test As String

Dim Result As String

ScoreC1 = Range("Risk_ScoreC1").Value
ScoreC2 = Range("Risk_ScoreC2").Value
test = Range("RiskProfile_Test").Value

Select Case Range("Relationship_StatusC1")

Case "Single", "Seperated", "Divorced", "Widowed"


If test = "Lifespan Risk Tolerance Score" And ScoreC1 < 14 Then

Result = "Cash Management"

ElseIf test = "Lifespan Risk Tolerance Score" And 13 < ScoreC1 < 19 Then

Result = "Conservative"

ElseIf test = "Lifespan Risk Tolerance Score" And 18 < ScoreC1 < 24 Then

Result = "Moderatively Conservative"

ElseIf test = "Lifespan Risk Tolerance Score" And 23 < ScoreC1 < 29 Then

Result = "Balanced"

ElseIf test = "Lifespan Risk Tolerance Score" And 28 < ScoreC1 < 34 Then

Result = "Growth"

ElseIf test = "Lifespan Risk Tolerance Score" And ScoreC1 > 33 Then

Result = "High Growth"


ElseIf test = "M3 Risk Profile Questionnaire Completed" And ScoreC1 < 18 Then

Result = "Cash Management"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And 17 < ScoreC1 < 36 Then

Result = "Conservative"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And 35 < ScoreC1 < 55 Then

Result = "Moderatively Conservative"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And 54 < ScoreC1 < 75 Then

Result = "Balanced"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And 74 < ScoreC1 < 88 Then

Result = "Growth"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And ScoreC1 > 87 Then

Result = "High Growth"

End If


Case "Married", "DeFacto", "Previously Divorced and Remarried", "Previously Divorced and DeFacto", "Previously Widowed and Remarried", "Previously Widowed and DeFacto"


If test = "Lifespan Risk Tolerance Score" And (ScoreC1 + ScoreC2) / 2 < 14 Then

Result = "Cash Management"

ElseIf test = "Lifespan Risk Tolerance Score" And 13 < (ScoreC1 + ScoreC2) / 2 < 19 Then

Result = "Conservative"

ElseIf test = "Lifespan Risk Tolerance Score" And 18 < (ScoreC1 + ScoreC2) / 2 < 24 Then

Result = "Moderatively Conservative"

ElseIf test = "Lifespan Risk Tolerance Score" And 23 < (ScoreC1 + ScoreC2) / 2 < 29 Then

Result = "Balanced"

ElseIf test = "Lifespan Risk Tolerance Score" And 28 < (ScoreC1 + ScoreC2) / 2 < 34 Then

Result = "Growth"

ElseIf test = "Lifespan Risk Tolerance Score" And (ScoreC1 + ScoreC2) / 2 > 33 Then

Result = "High Growth"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And (ScoreC1 + ScoreC2) / 2 < 18 Then

Result = "Cash Management"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And 17 < (ScoreC1 + ScoreC2) / 2 < 36 Then

Result = "Conservative"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And 35 < (ScoreC1 + ScoreC2) / 2 < 55 Then

Result = "Moderatively Conservative"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And 54 < (ScoreC1 + ScoreC2) / 2 < 75 Then

Result = "Balanced"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And 74 < (ScoreC1 + ScoreC2) / 2 < 88 Then

Result = "Growth"

ElseIf test = "M3 Risk Profile Questionnaire Completed" And (ScoreC1 + ScoreC2) / 2 > 87 Then

Result = "High Growth"

End If


End Select


riskprofile = Result


End Function

1 Reply

If possible, kindly share the sheet@KyHeffernan