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.
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.
Last edited: