VBA: Tax Schedule: Making dynamic

[Sparty]

Hello folks, I'm new to VBA and need help making the following code dynamic:

Sub thisnthat()

Dim breakPoint
Dim taxPercent
Dim incomeAmount As Double
Dim taxAmount As Double
Dim rowCount As Integer
Dim lastRow As Long

incomeAmount = Round(Range("d4").Value)

Range("d4").Value = incomeAmount

With Worksheets("Model").Range("d4"<wbr>)
lastRow = Range(.Offset(0, 0), .End(xlDown)).Rows.Count

Do Until .Offset(rowCount, 0).Value = ""

Select Case incomeAmount 'check

Case Is > 75000 'check
taxAmount = incomeAmount * 0.3

Case Is = 75000 'check
taxAmount = incomeAmount * 0.25

Case Is >= 55000 'check
taxAmount = incomeAmount * 0.2

Case Is >= 35000 'check
taxAmount = incomeAmount * 0.15

Case Else
taxAmount = incomeAmount * 0

End Select 'check
Exit Do

rowCount = rowCount + 1

Loop
End With

Range("e4").Value = taxAmount

I've experimented with a few things but don't know enough to get the right syntax.

Below is the screenshot of what my code does (fills in cell E4 correctly- I want it to fill in all the gray squares, including if new income amounts are added to column D)

 

[RobertSF]

I can't put the code together now, but this looks like a good case for a UDF -- User Defined Function. You would use it in every gray cell just like a regular Excel function, something like =TAX(D4, $A$4:$B$8), where D4 is the income and $A$4:$B$8 is the tax table range. You'd want absolute reference if you're going to copy the cell down the gray range. I hope this tip points you in the right direction.

 

[Sparty]

Thanks for the tip, looks like I asked a complicated question!

I have a simpler one if you (or someone) has the time. I'm trying to remember how to do vlookup and something isn't working out quite right.

These are the first few numbers in my array:

[TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl64, width: 65, align: right"]101[/TD]
[TD="class: xl64, width: 65, align: right"]747[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]102[/TD]
[TD="class: xl64, align: right"]3643[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]103[/TD]
[TD="class: xl64, align: right"]5093[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]104[/TD]
[TD="class: xl64, align: right"]1752[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]105[/TD]
[TD="class: xl64, align: right"]2723[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]106[/TD]
[TD="class: xl64, align: right"]928[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]107[/TD]
[TD="class: xl64, align: right"]4048[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]108[/TD]
[TD="class: xl64, align: right"]1293[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]109[/TD]
[TD="class: xl64, align: right"]3784[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]110[/TD]
[TD="class: xl64, align: right"]6674[/TD]
[/TR]
</tbody>[/TABLE]


If the #s in B are > 1500 I'm trying to bring the numbers in column A & B over to a new tab in my spreadsheet. This is what I'm using so far that isn't working (data is the name of the tab with the information in it):

=IF(Data!$B4>1500,VLOOKUP(Data!$B4,Data!$A$4:$B$549,1,FALSE),"")

 

[Marcelo Branco]

Regarding post 1

Try

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Breakpoint​

[/td][td]

Tax​

[/td][td][/td][td]

Income​

[/td][td]

Tax Amount​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

15000​

[/td][td]

0%​

[/td][td][/td][td]

112422​

[/td][td]

33726,6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

35000​

[/td][td]

15%​

[/td][td][/td][td]

48037​

[/td][td]

7205,55​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

55000​

[/td][td]

20%​

[/td][td][/td][td]

19928​

[/td][td]

0​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

75000​

[/td][td]

25%​

[/td][td][/td][td]

74482​

[/td][td]

14896,4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

75000,01​

[/td][td]

30%​

[/td][td][/td][td]

68543​

[/td][td]

13708,6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td]

141486​

[/td][td]

42445,8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td][/td][td]

81066​

[/td][td]

24319,8​

[/td][/tr]
[/table]

Formula in E2 copied down
=IF(D2<15000,0,D2*VLOOKUP(D2,A$2:B$6,2))

M.

 

[MARK858]

@Sparky, are you sure that your tax figure of 33727 is correct as tax rates in most countries is cumulative i.e. if you earned 40000 15000 would be 0 tax the next 20000 would get taxed at 15% and the remaining 5000 at 20%.
Your figure seems to tax the whole amount (including the zero rated threshold) at the top rate i.e. 112422 x 30%

For 112422 if it was cumulative it would break down to

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td]

20000​

[/td][td]

3000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td]

20000​

[/td][td]

4000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

15​

[/td][td]

20000​

[/td][td]

5000​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

16​

[/td][td]

37422​

[/td][td]

11226.6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

17​

[/td][td]Total[/td][td]

23226.6​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

The formula to work out the above using the table is as below...

Excel Workbook
	A	B	C	D	E
1	Breakpoint	Tax	Diifferential	Income	Tax Amount
2	15000	15%	0.15	112422	23226.6
3	35000	20%	0.05	48037	5607.4
4	55000	25%	0.05	19928	739.2
5	75000	30%	0.05	74482	11870.5
6				68543	10385.75
7				141486	31945.8
8				81066	13819.8
Sheet4

 

[MARK858]

Sorry should have tagged @Sparty not Sparky in the previous post.
Just while I am posting this I might as well post the formula if there was no lookup table which is...

=SUMPRODUCT(--(D2>{15000;35000;55000;75000}), (D2-{15000;35000;55000;75000}), {0.15;0.05;0.05;0.05})