Apr 23 2020 10:40 PM
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
Apr 24 2020 01:16 AM
If possible, kindly share the sheet@KyHeffernan