Select case and if funcitons

%3CLINGO-SUB%20id%3D%22lingo-sub-1334731%22%20slang%3D%22en-US%22%3ESelect%20case%20and%20if%20funcitons%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1334731%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I'm%20trying%20to%20get%20a%20vba%20module%20to%20apply%20a%20different%20value%20depending%20on%20a%20test%20selected%20in%20a%20dropdown%20box%2C%20a%20test%20score%20between%20one%20or%20two%20people%2C%20and%20if%20two%20people%20have%20done%20the%20test%2C%20i'd%20like%20to%20give%20an%20average%20result%20based%20on%20their%20scores.%3C%2FP%3E%3CP%3EI%20made%20a%20super%20convoluted%20attempt%20at%20this%2C%20but%20it's%20not%20working%20and%20I've%20hit%20a%20block.%3C%2FP%3E%3CP%3EHow%20would%20I%20go%20about%20approaching%20this%3F%3C%2FP%3E%3CP%3EThanks%20heaps%20for%20any%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20attempt%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EPublic%20Function%20riskprofile(ScoreC1%2C%20ScoreC2%20As%20Double)%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EDim%20test%20As%20String%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EDim%20Result%20As%20String%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3EScoreC1%20%3D%20Range(%22Risk_ScoreC1%22).Value%3CBR%20%2F%3EScoreC2%20%3D%20Range(%22Risk_ScoreC2%22).Value%3CBR%20%2F%3Etest%20%3D%20Range(%22RiskProfile_Test%22).Value%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3ESelect%20Case%20Range(%22Relationship_StatusC1%22)%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3ECase%20%22Single%22%2C%20%22Seperated%22%2C%20%22Divorced%22%2C%20%22Widowed%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%20ScoreC1%20%26lt%3B%2014%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Cash%20Management%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%2013%20%26lt%3B%20ScoreC1%20%26lt%3B%2019%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Conservative%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%2018%20%26lt%3B%20ScoreC1%20%26lt%3B%2024%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Moderatively%20Conservative%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%2023%20%26lt%3B%20ScoreC1%20%26lt%3B%2029%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Balanced%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%2028%20%26lt%3B%20ScoreC1%20%26lt%3B%2034%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Growth%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%20ScoreC1%20%26gt%3B%2033%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22High%20Growth%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%20ScoreC1%20%26lt%3B%2018%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Cash%20Management%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%2017%20%26lt%3B%20ScoreC1%20%26lt%3B%2036%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Conservative%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%2035%20%26lt%3B%20ScoreC1%20%26lt%3B%2055%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Moderatively%20Conservative%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%2054%20%26lt%3B%20ScoreC1%20%26lt%3B%2075%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Balanced%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%2074%20%26lt%3B%20ScoreC1%20%26lt%3B%2088%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Growth%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%20ScoreC1%20%26gt%3B%2087%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22High%20Growth%22%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CBR%20%2F%3ECase%20%22Married%22%2C%20%22DeFacto%22%2C%20%22Previously%20Divorced%20and%20Remarried%22%2C%20%22Previously%20Divorced%20and%20DeFacto%22%2C%20%22Previously%20Widowed%20and%20Remarried%22%2C%20%22Previously%20Widowed%20and%20DeFacto%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2014%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Cash%20Management%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%2013%20%26lt%3B%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2019%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Conservative%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%2018%20%26lt%3B%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2024%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Moderatively%20Conservative%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%2023%20%26lt%3B%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2029%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Balanced%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%2028%20%26lt%3B%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2034%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Growth%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22Lifespan%20Risk%20Tolerance%20Score%22%20And%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26gt%3B%2033%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22High%20Growth%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2018%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Cash%20Management%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%2017%20%26lt%3B%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2036%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Conservative%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%2035%20%26lt%3B%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2055%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Moderatively%20Conservative%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%2054%20%26lt%3B%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2075%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Balanced%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%2074%20%26lt%3B%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26lt%3B%2088%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22Growth%22%3CBR%20%2F%3E%3CBR%20%2F%3EElseIf%20test%20%3D%20%22M3%20Risk%20Profile%20Questionnaire%20Completed%22%20And%20(ScoreC1%20%2B%20ScoreC2)%20%2F%202%20%26gt%3B%2087%20Then%3CBR%20%2F%3E%3CBR%20%2F%3EResult%20%3D%20%22High%20Growth%22%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Select%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3Eriskprofile%20%3D%20Result%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1334731%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1335040%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20case%20and%20if%20funcitons%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1335040%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20possible%2C%20kindly%20share%20the%20sheet%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F575759%22%20target%3D%22_blank%22%3E%40KyHeffernan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted

If possible, kindly share the sheet@KyHeffernan