ExcelBanter - View Single Post

stnkynts

I am not sure why it is giving you that error. Another way you can do this is through the use of a macro. While I usually never recommend a macro where a formula could be used in this scenario it would work easily. Furthermore a macro would make it a lot easier to change values in the future if necessary.

Here is the start of the code

Code:

Option Explicit

Sub Macro1()
Dim Lastrow As Integer
Dim a As Integer

Lastrow = Range("D" & Rows.Count).End(xlUp).Row

For a = 1 To Lastrow
    Select Case Range("D" & a).Value
        Case 180000 To 249999
            Range("E" & a) = Range("D" & a) * 0.005
        Case 250000 To 349999
            Range("E" & a) = Range("D" & a) * 0.0075
        'etc etc etc
    End Select
Next a

End Sub

I made a couple of assumptions here. First is that all the values you are looking through are in column D (ex. D21, D22, D23, etc). Second your IF statement was in column E(ex. E21, E22, E23, etc respectively).

You will need to finish adding the cases for all the different percentages but it shouldnt take you too long. TEST THIS CODE ON A COPY OF YOUR SHEET.
It will overwrite stuff. Let me know how it works.