Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Trim() function chain-link. Please Help me...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Trim() function chain-link. Please Help me...
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!

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
Jay Jay is offline
external usenet poster
  
Posts: 671
Default Trim() function chain-link. Please Help me...
Hi Thoren_dragonslayer -

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

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


" wrote:

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!


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 329
Default Trim() function chain-link. Please Help me...
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]
-------------------------

wrote in message oups.com...
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!

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default Trim() function chain-link. Please Help me...
On Mar 20, 8:13 pm, "macropod" wrote:
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]
-------------------------



wrote in ooglegroups.com...
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!- Hide quoted text -

- Show quoted text -

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!

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Function =Trim() Dowitch Excel Worksheet Functions 1 April 29th 09 06:49 PM
Len & Trim Function Daren Excel Worksheet Functions 6 October 14th 08 02:32 PM
TRIM function fitou_learn[_2_] New Users to Excel 2 June 12th 07 11:06 AM
Help with TRIM Function Willie T Excel Programming 3 January 8th 05 02:43 AM
Trim function in VBA molavi111 Excel Programming 3 September 22nd 04 05:59 PM


All times are GMT +1. The time now is 04:15 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement