# Help with If Statement

#### Camacho86

##### New Member
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

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### AlphaFrog

##### MrExcel MVP
Hi and welcome to the forum.

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

Code:
``````[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]``````

Last edited:

Replies
9
Views
271
Replies
7
Views
385
Replies
4
Views
299
Replies
5
Views
299
Replies
5
Views
241

1,195,743
Messages
6,011,393
Members
441,610
Latest member
Shakeable_Drip

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back