Custom VBA Function - Tax Calculator - Need Help!

[JohnP423]

First, I tried downloading the VB-HTML Maker add in and couldn't get it to work. So I apologize if I am breaking the rules.

PROBLEM: Create a VBA function called TaxCalculator that accepts the taxable income from a cell and status from another one and generates the Federal Income Tax using tables

Relevant Information: In my work book I have created two tables. One for filing "Single" (named ScheduleX) and one for filing "Married" (named ScheduleY).

The fist column is "Taxable Income over.." (i.e the BaseIncome). The second column is the applicable tax rate (i.e TaxRate). The third column is the base amount of tax owed (i.e BaseAmt)

What I Got:

Function TaxCalculator(x, y)

'x= Taxable Income
'y = Filing Status

BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleX"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleX"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleX"), 2) / 100

Tax = BaseAmt + (x - BaseIncome) * TaxRate

TaxCalculator = Tax

End Function
The problem with this function is it won't change use the applicable table based on the contents of y. Thus when the cell that y references contains "Single" I need the function to use Range("ScheduleX") in the VLookup and when the cell contains "Married" I need the function to use Range("ScheduleY") in the VLookup.

Could anyone please offer advice? Thanks much in advance for your time and help.

I am using Excel 2007 on a Windows Vista laptop.

 

[JohnP423]

Here is what I used:

Function TaxCalculator(x, y)

'x= Taxable Income
'y = Filing Status

If y = "Single" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleX"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleX"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleX"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

ElseIf y = "Married" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleY"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleY"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleY"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

ElseIf y = "Widow" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleY"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleY"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleY"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

ElseIf y = "Married Seperate" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleXX"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleXX"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleXX"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

ElseIf y = "Head of Household" Then
BaseAmt = WorksheetFunction.VLookup(x, Range("ScheduleYY"), 3)
BaseIncome = WorksheetFunction.VLookup(x, Range("ScheduleYY"), 1)
TaxRate = WorksheetFunction.VLookup(x, Range("ScheduleYY"), 2) / 100
Tax = BaseAmt + (x - BaseIncome) * TaxRate
TaxCalculator = Tax

Else
Msg = "Please use Single, Married, Widow, Married Seperate, or Head of Household as the filing status"

MsgBox Msg

End If

End Function

 

[sharonritchey]

As we all know, an income is really taxable. That is an infallible truth. All revenue a person earns should be paid or else he may end up needing cash advances to pay all those extra taxes especially if he haven't been reporting all of the revenues. Therefore, having this income calculator, a person can easily calculate his income. It will be a good intervention to monitor the taxes to be paid.
https://personalmoneynetwork.com/cash-advance/