Results 1 to 2 of 2

Thread: Moving code to a function - not working
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Moving code to a function - not working

    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?

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,722
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Moving code to a function - not working

    Hi

    I don't have excel here but I don't remember a method IF in the WorksheetFunction object.

    I've just checked the help here and I don't see it.

    https://docs.microsoft.com/en-us/off...ksheetfunction

    Please check.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •