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?
 

Forum statistics

Threads
1,077,823
Messages
5,336,570
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top