stuck

[charlie11k]

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?

 

[VoG]

Try

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

 

[charlie11k]

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

 

[Weaver]

possibly:

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

 

[VoG]

Try

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

 

[tusharm]

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.

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

    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
Create a Self-Adjusting Range
http://www.tushar-mehta.com/publish_train/xl_vba_cases/0101_Create_a_self_adjusting_range.htm

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?

 

[charlie11k]

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?

 

[charlie11k]

Does my question make sense?

 

[tusharm]

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?

 

[charlie11k]

How can I put this in macro form?