How to have formula auto fill down a row

[blu817]

Trying to do some income tax reporting for example if A1 income is X multiply by the following if its X do this instead. I am fairly new to VBA but how do I make it auto fill a range for example A1:A10? Here is the code I wrote and if there are better ideas I am open to that.




Dim income As Double
Dim tax As Double


income = Round(Range("A2").Value)
Range("A2").Value = income


Select Case income
Case Is >= 180001
tax = 55850 + 0.45 * (income - 180000)
Case Is > 80001
tax = 17850 + 0.38 * (income - 80000)
Case Is >= 35001
tax = 4350 + 0.3 * (income - 35000)
Case Is >= 6001
tax = 0.15 * (income - 6000)
Case Else
tax = 0

End Select


Range("B2").Value = tax




End Sub

 

[blu817]

to add some clarity I would like the calculation in column B

 

[MrTeeny]

You can use For and next to loop though data, you simply replace the row number with your changing variable, you'd stick your select case income in a function but just put it up quickly so you'd get an idea of the for next loops

Sub tax()
Dim income As Double
Dim tax As Double
Dim i As Integer






For i = 1 To 10  ' i.e. A1 to A10


income = Round(Range("A" & i).Value)
Range("A" & i).Value = income




Select Case income
Case Is >= 180001
tax = 55850 + 0.45 * (income - 180000)
Case Is > 80001
tax = 17850 + 0.38 * (income - 80000)
Case Is >= 35001
tax = 4350 + 0.3 * (income - 35000)
Case Is >= 6001
tax = 0.15 * (income - 6000)
Case Else
tax = 0


End Select




Range("B" & i).Value = tax


Next i




End Sub

 

[blu817]

@MrTeeny it worked like a charm!

What would I have to change on my code if column varied in number? For this one I knew my last row was 10 but my next one could be 22.

 

[Joe4]

Try this variation:

Sub tax()

[COLOR=#ff0000]    Dim lastRow As Long[/COLOR]
    Dim income As Double
    Dim tax As Double
    Dim i As Integer

[COLOR=#ff0000]'   Find last row in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row[/COLOR]
[COLOR=#ff0000][/COLOR]
    For i = 1 To [COLOR=#ff0000]lastRow[/COLOR]
        income = Round(Range("A" & i).Value)
        Range("A" & i).Value = income

        Select Case income
            Case Is >= 180001
                tax = 55850 + 0.45 * (income - 180000)
            Case Is > 80001
                tax = 17850 + 0.38 * (income - 80000)
            Case Is >= 35001
                tax = 4350 + 0.3 * (income - 35000)
            Case Is >= 6001
                tax = 0.15 * (income - 6000)
            Case Else
                tax = 0
        End Select

        Range("B" & i).Value = tax
    Next i

End Sub

 

[blu817]

you guys are awesome. thanks for the help!