Moving code to a function - not working

troels

New Member
Joined
Jan 11, 2018
Messages
5
This is similar to an issue i posted last year. I have an Excel formula and reference data taken from the ATO site and used to calculate PAYG tax, based on monthly income.

When I apply the code:
Code:
=ROUND((ROUND((TRUNC((3/13)*(D131+IF(ISNUMBER(SEARCH(".33",D131)),0.01,0)),0)+0.99)*(VLOOKUP((TRUNC((3/13)*(D131+IF(ISNUMBER(SEARCH(".33",D131)),0.01,0)),0)),LU2A,2))-(VLOOKUP((TRUNC((3/13)*(D131+IF(ISNUMBER(SEARCH(".33",D131)),0.01,0)),0)),
to a given Cell (A131 in the above), it works fine.

I tried to create a function to replicate the above and just keep getting "#VALUE!". Here's my function:

Code:
Function Monthly(ByVal GrossPay As Double) As Double

    Monthly = Round((Round((Application.WorksheetFunction.Trunc((3 / 13) * (GrossPay + Application.WorksheetFunction.IF(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(".33", GrossPay)), 0.01, 0)), 0) + 0.99) * (Application.WorksheetFunction.VLookup((Application.WorksheetFunction.Trunc((3 / 13) * (GrossPay + Application.WorksheetFunction.IF(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(".33", GrossPay)), 0.01, 0)), 0)), Range("LU2A"), 2)) - (Application.WorksheetFunction.VLookup((Trunc((3 / 13) * (GrossPay + Application.WorksheetFunction.IF(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(".33", GrossPay)), 0.01, 0)), 0)), Range("LU2A"), 3)), 0) * (13 / 3)), 0)


End Function
Its the same code but with explicit calls to IF, Search, VLOOKUP and use of "Range".

Any ideas?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Watch MrExcel Video

Forum statistics

Threads
1,089,880
Messages
5,410,935
Members
403,335
Latest member
ddaveryos

This Week's Hot Topics

Top