Trim() function chain-link. Please Help me...

  • Thread starter thoren_dragonslayer
  • Start date
T

thoren_dragonslayer

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!
 
J

Jay

Hi Thoren_dragonslayer -

You have a battery of 3 statements that repeat after each If...End If block.
These are:

Unload Me
Selection.Show
Worksheets("Income").Protect password:="example"

I suggest you delete them all except for the last set (in other words, leave
the set of three statements just before the End Sub.

What is happening now is that if Me.Household.Value <> "1 Person" the form
unloads and Me.Household.Value goes out of scope and is no longer available
for the other If...End If blocks.

--
Jay


[email protected] said:
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!
Click to expand...
 
M

macropod

Hi,

I think you'll find this construction will work better:

Private Sub CommandButton1_Click()
Dim Divisor As Long
Dim Pwd As String
Pwd = "example"
'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
Select Case Left(Trim(Me.Household.Value), InStr(Trim(Me.Household.Value), " ") - 1)
Case 1
Divisor = 10210
Case 2
Divisor = 13690
Case 3
Divisor = 17170
Case 4
Divisor = 20650
Case 5
Divisor = 24130
' add the remaining cases
Case Else
' what else to do if you get something unexpected
End Select
With Worksheets("Income")
.Unprotect Password:=Pwd
.Range("E2").Value = File.Value
.Range("H53").Value = Household.Value
.Range("C53").Value = "=(D18+H18+D31+H31+D44+H44+D50+H50)/" & Divisor
.Protect Password:=Pwd
Unload Me
Selection.Show
End With
End Sub

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

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!
Click to expand...
 
T

thoren_dragonslayer

Hi,

I think you'll find this construction will work better:

Private Sub CommandButton1_Click()
Dim Divisor As Long
Dim Pwd As String
Pwd = "example"
'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
Select Case Left(Trim(Me.Household.Value), InStr(Trim(Me.Household.Value), " ") - 1)
Case 1
Divisor = 10210
Case 2
Divisor = 13690
Case 3
Divisor = 17170
Case 4
Divisor = 20650
Case 5
Divisor = 24130
' add the remaining cases
Case Else
' what else to do if you get something unexpected
End Select
With Worksheets("Income")
.Unprotect Password:=Pwd
.Range("E2").Value = File.Value
.Range("H53").Value = Household.Value
.Range("C53").Value = "=(D18+H18+D31+H31+D44+H44+D50+H50)/" & Divisor
.Protect Password:=Pwd
Unload Me
Selection.Show
End With
End Sub

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------



Hi everyone,
Click to expand...
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...
Click to expand...
My Code:
Click to expand...
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
Click to expand...
Range("H53").Select
ActiveCell.Offset(0, 0).Select
ActiveCell.Value = Household.Value
Click to expand...
'Tell the workers to not leave these 2 options blank
Click to expand...
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
Click to expand...
'this is the string that I'm trying to get to work: Tells to watch for
entry, then move result to Cell C53
Click to expand...
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"
Click to expand...
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"
Click to expand...
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"
Click to expand...
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"
Click to expand...
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"
Click to expand...
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"
Click to expand...
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"
Click to expand...
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"
Click to expand...
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"
Click to expand...
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"
Click to expand...
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"
Click to expand...
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
Click to expand...
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!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you for your quick responces! I greatly appreciate the time both
of you took to look into my post and code. It's now runing perfectly
and I can now distribute this form. I greatly appreciate everything
you've done to help me out on this problem!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

type mismatch error in macro in excel 0
Macro to delete row if cells greater than certain value 1
Manpower Calendar Distribution, Skipping weekends. 0
Dividelink 0
Hidden sheet bleeds through 3
Cleaner coding to repeat process 7
Help with Drop-down control 0
display calculation next row 5

Top