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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Watch MrExcel Video

Forum statistics

Threads
1,090,234
Messages
5,413,226
Members
403,468
Latest member
adenard

This Week's Hot Topics

Top