Hi everyone,

I'm in a deep predicament. I'm creating this spreadsheet calculation
sheet for my workplace and I'm having trouble implementing the final
piece. Everything else is configured. What I'm trying to do is, when
the user opens the excel file, it automatically opens a quick
assessment user form that asks them 2 questions for the calc. sheet...
Now I'm trying to show the FPIL percentage based off of client's
income dependent on the number in the household. How I've configured
that is I used the Trim() function... I have a total of up to 12
people in the Household and a chain of Trim functions to determine the
100% FPIL guideline for each one... well my code might better explain
that.... The problem is, My Code will only detect the first Trim()
Function for "1 Person". Is there anyway or what is it that I"m doing
wrong that is preventing my string of codes to not recognize the other
choices such as "2 People", "3 People" etc. I appreciate any help...

My Code:

Private Sub CommandButton1_Click()
Worksheets("Income").Unprotect password:="example"
'where to put the data from combobox
ActiveWorkbook.Sheets("Income").Activate
Range("E2").Select
ActiveCell.Offset(0, 0).Select
ActiveCell.Value = File.Value

Range("H53").Select
ActiveCell.Offset(0, 0).Select
ActiveCell.Value = Household.Value

'Tell the workers to not leave these 2 options blank

If Trim(Me.File.Value) = "" Then
MsgBox "Please Select the date this case was opened.", vbExclamation,
"Missing Information"
Me.File.SetFocus
Exit Sub
End If
If Trim(Me.Household.Value) = "" Then
MsgBox "Please Select the Number of people living in the Household.",
vbExclamation, "Missing Information"
Me.Household.SetFocus
Exit Sub
End If
Unload Me
Selection.Show

'this is the string that I'm trying to get to work: Tells to watch for
entry, then move result to Cell C53

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "1 Person" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
10210"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "2 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
13690"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "3 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
17170"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "4 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
20650"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "5 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
24130"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "6 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
27610"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "7 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
31090"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "8 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
34570"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "9 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
38050"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "10 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
41530"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "11 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
45010"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

Worksheets("Income").Unprotect password:="example"
If Trim(Me.Household.Value) = "12 People" Then
Range("C53").Select
ActiveCell.FormulaR1C1 =
"=(R[-35]C[1]+R[-35]C[5]+R[-22]C[1]+R[-22]C[5]+R[-9]C[1]+R[-9]C[5]+R[-3]C[1]+R[-3]C[5])/
48490"
Exit Sub
End If
Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"
End Sub

As you can see I have a long stream of If()'s and it only recognizes
the "1 Person" String and no more. So it doesn't fill in cell C53 with
the results with the rest if the user selects Households larger than
2. Please help me with any suggestions. Thank you!