# stuck

#### charlie11k

##### New Member
Here is what i have..
Sub Totals()
Dim i As Long, Multiplier As Double, aSum As Double
aSum = WorksheetFunction.Sum(Range("E224:E264"))
Select Case aSum
Case Is < 6000: Multiplier = 0
Case Is < 7000: Multiplier = 0.01
Case Is < 8000: Multiplier = 0.02
Case Is < 9000: Multiplier = 0.04
Case Is < 10000: Multiplier = 0.05
Case Is < 11000: Multiplier = 0.06
Case Is < 12000: Multiplier = 0.08
Case Is >= 12000: Multiplier = 0.1
End Select
For i = 224 To 264
Range("H" & i).Value = Range("E" & i).Value * Multiplier
Next i
End Sub

I need for a value not to be put into the "H" cell if cell "A" on that row has a value of either y1, y2, y3, or y4. How can i modify this?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Try

Code:
Sub Totals()
Dim i As Long, Multiplier As Double, aSum As Double
aSum = WorksheetFunction.Sum(Range("E224:E264"))
Select Case aSum
Case Is < 6000: Multiplier = 0
Case Is < 7000: Multiplier = 0.01
Case Is < 8000: Multiplier = 0.02
Case Is < 9000: Multiplier = 0.04
Case Is < 10000: Multiplier = 0.05
Case Is < 11000: Multiplier = 0.06
Case Is < 12000: Multiplier = 0.08
Case Is >= 12000: Multiplier = 0.1
End Select
For i = 224 To 264
With Range("A" & i)
If .Value <> "y1" And .Value <> "y2" And .Value <> "y3" And .Value <> "y4" Then
Range("H" & i).Value = Range("E" & i).Value * Multiplier
End If
End With
Next i
End Sub

I forgot.....If there is a value in "A" then "H" cannot exceed the .05 percentage......capping off at the less than 11000 mark.

possibly:

Code:
Sub Totals()
Dim i As Long, Multiplier As Double, aSum As Double
aSum = WorksheetFunction.Sum(Range("E224:E264"))
Select Case aSum
Case Is < 6000: Multiplier = 0
Case Is < 7000: Multiplier = 0.01
Case Is < 8000: Multiplier = 0.02
Case Is < 9000: Multiplier = 0.04
Case Is < 10000: Multiplier = 0.05
Case Is < 11000: Multiplier = 0.06
Case Is < 12000: Multiplier = 0.08
Case Is >= 12000: Multiplier = 0.1
End Select
For i = 224 To 264
Select Case Range("A" & i)
Case "y1", "y2", "y3", "y4"
Range("H" & i).Value = Range("E" & i).Value * Multiplier
End Select
Next i
End Sub

Try

Code:
Sub Totals()
Dim i As Long, Multiplier As Double, aSum As Double
aSum = WorksheetFunction.Sum(Range("E224:E264"))
Select Case aSum
Case Is < 6000: Multiplier = 0
Case Is < 7000: Multiplier = 0.01
Case Is < 8000: Multiplier = 0.02
Case Is < 9000: Multiplier = 0.04
Case Is < 10000: Multiplier = 0.05
Case Is < 11000: Multiplier = 0.06
Case Is < 12000: Multiplier = 0.08
Case Is >= 12000: Multiplier = 0.1
End Select
For i = 224 To 264
With Range("A" & i)
If .Value <> "y1" And .Value <> "y2" And .Value <> "y3" And .Value <> "y4" Then
If .Value <> "" Then
Range("H" & i).Value = Range("E" & i).Value * WorksheetFunction.Min(Multiplier, 0.05)
Else
Range("H" & i).Value = Range("E" & i).Value * Multiplier
End If
End If
End With
Next i
End Sub

If one can enter a formula in all relevant cells in H, I would then find it hard to justify using code to solve this problem. It is easily solved in Excel itself with a structure that will be both that much more transparent and easy to maintain.

Suppose you have the "leave blank list" (y1, y2, y3, y4 as of now) in a cell range. Name this range LeaveBlankList.

Put the range of values and multipliers in 2 adjacent columns. Name this range MultTable.

Code:
0	0
6000	0.01
7000	0.02
8000	0.04
9000	0.05
10000	0.06
11000	0.08
12000	0.1

Now, in H224 enter the formula =IF(ISERROR(MATCH(\$A224,LeaveBlankList,0)),\$E224*VLOOKUP(SUM(\$E\$224:\$E\$264),MultTable,2,TRUE),"")

Copy H224 down to H225:H264.

To enter the formula by code, use the one statement
Code:
Range("H224:H264").FormulaR1C1 = _
"=IF(ISERROR(MATCH(RC1,LeaveBlankList,0)),RC5*VLOOKUP(SUM(R224C5:R264C5),MultTable,2,TRUE),"""")"

You can even make the solution self-adjusting to changes to either table. To use self-adjusting ranges, see

Here is what i have..
Sub Totals()
Dim i As Long, Multiplier As Double, aSum As Double
aSum = WorksheetFunction.Sum(Range("E224:E264"))
Select Case aSum
Case Is < 6000: Multiplier = 0
Case Is < 7000: Multiplier = 0.01
Case Is < 8000: Multiplier = 0.02
Case Is < 9000: Multiplier = 0.04
Case Is < 10000: Multiplier = 0.05
Case Is < 11000: Multiplier = 0.06
Case Is < 12000: Multiplier = 0.08
Case Is >= 12000: Multiplier = 0.1
End Select
For i = 224 To 264
Range("H" & i).Value = Range("E" & i).Value * Multiplier
Next i
End Sub

I need for a value not to be put into the "H" cell if cell "A" on that row has a value of either y1, y2, y3, or y4. How can i modify this?

Not trying to be difficult here but I think I need to change it up a bit. Here is what I would like:

In column "I" have formulas that calculate a percentage from Column E. What I really need is something like this:

If E224 through E264 total more than 10K then cell "I" respectively if cell A is populated with Y1, ,needs to multiply cell E by 19% and put that total in "I". How can I do this?

Does my question make sense?

In I224 enter the formula =IF(AND(SUM(\$E\$224:\$E\$264)>10000,\$A224="y1"),\$E224*19%,"") Copy I224 as far down as needed.
Not trying to be difficult here but I think I need to change it up a bit. Here is what I would like:

In column "I" have formulas that calculate a percentage from Column E. What I really need is something like this:

If E224 through E264 total more than 10K then cell "I" respectively if cell A is populated with Y1, ,needs to multiply cell E by 19% and put that total in "I". How can I do this?

How can I put this in macro form?

Replies
4
Views
142
Replies
9
Views
246
Replies
15
Views
393
Replies
5
Views
143
Replies
3
Views
449

1,203,187
Messages
6,053,992
Members
444,696
Latest member
VASUCH

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