Help with If Statement

[Camacho86]

Hi, I am new to VBA and need help finishing some code. I am trying to use ranges of incomes and the number of family members (i.e. 1, 2, 3, 4…) to calculate a percentage to see if families qualify for our program. This is because we have to refer to a chart with different income to family member ratios. I am using two different macros, the first triggers the second. On the second macro “NestedIf-Income”, I need to put code in at the end of each If statement which will factor in the number of family members for each row that has the respected family. Currently, I have “And Cells (14, 24) = 1” in that section. I do not know how to expand this range and still make it so that this code will be conscious of each row’s number of family members. Please help me out!

If Not Application.Intersect(Range("A1:A6"), Range(Target.Address)) Is Nothing Then
Call NestedIf_Income
End If
End Sub


Sub NestedIf_Income()




'Variable declaration
Dim Income As String

'Accepting the Percentage by the user
Income = InputBox("Enter the Income:", "Income")

If Income < 11499 And Cells(14, 24) = 1 Then
'Check if the Less than 30%
MsgBox "Income : Less than 30%"
ElseIf Income >= 11500 And Income < 113412 And Cells(14, 24) = 1 Then
'Check if the Income is 30%
MsgBox "Income : 30%"
ElseIf Income >= 13413 And Income < 17237 And Cells(14, 24) = 1 Then
'Check if the Income is 40%
MsgBox "Income : 40%"
ElseIf Income >= 17238 And Income < 21065 And Cells(14, 24) = 1 Then
'Check if the Income is 50%
MsgBox "Income : 50%"
ElseIf Income >= 21066 And Income < 24897 And Cells(14, 24) = 1 Then
'Check if the Income is 60%
MsgBox "Income : 60%"
ElseIf Income >= 21066 And Income < 24897 And Cells(14, 24) = 1 Then
'Check if the Income is 70%
MsgBox "Income : 70%"
ElseIf Income >= 21066 And Income < 24897 And Cells(14, 24) = 1 Then
'Check if the Income is 80%
MsgBox "Income : 80%"
ElseIf Income >= 21066 And Income < 24897 And Cells(14, 24) = 1 Then
'Check if the Income is More than 80%
MsgBox "Income : More than 80%"
Else
'Check if the Income has fail
MsgBox "Income : Need Percentage"
End If


End Sub

 

[AlphaFrog]

Hi and welcome to the forum.

Try something like this.
Some of the Income levels still need to be filled in.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    
    [COLOR=green]'Variable declaration[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Income [COLOR=darkblue]As[/COLOR] Single, Percentage [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Intersect(Range("A1:A6"), Target) [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Count = 1 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] Cells(Target.Row, 24) = 1 [COLOR=darkblue]Then[/COLOR]
        
                [COLOR=green]'Accepting the Percentage by the user[/COLOR]
                Income = Application.InputBox("Enter the Income:", "Income", Type:=1)
                [COLOR=darkblue]If[/COLOR] Income = 0 [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR] [COLOR=green]'user canceled[/COLOR]
                
                [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Income
                    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] < 11500: Percentage = 0.3
                    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] < 13413: Percentage = 0.4
                    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] < 17238: Percentage = 0.5
                    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] < 21066: Percentage = 0.6
                    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] < 24897: Percentage = 0.7
                    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] < [COLOR=#ff0000]24897[/COLOR]: Percentage = 0.8   [COLOR=green]'???[/COLOR]
                    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] < [COLOR=#ff0000]24897[/COLOR]: Percentage = 0.9   [COLOR=green]'???[/COLOR]
                    [COLOR=darkblue]Case[/COLOR] [COLOR=darkblue]Is[/COLOR] < [COLOR=#ff0000]24897[/COLOR]: Percentage = 1     [COLOR=green]'???[/COLOR]
                    [COLOR=green]'Case Else: Percentage = 0[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
                
                MsgBox "Less than " & Format(Percentage, "0%"), , "Income"
                
           [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]