Consulting

Results 1 to 14 of 14

Thread: Wrong result of my macro!

  1. #1

    Wrong result of my macro!

    Hello!

    I would like to ask you what is wrong in my code, because I do not know what happened.
    In the table I have 2 columns: Salary and Has Kids (Do someone have children?).
    My macro take Salary and answer from columns Has Kids (Yes or No) and give particularly TaxRate.
    I have problem because for example: If I have Salary 3000 and "Yes" in columns Has Kids, macro give me that result: 0.01 (It should be 0).
    Please help me, because I do not know what do I wrong.
    Sorry for my English mistakes
    Function getTaxRateElseNestedIF(Salary As Double, HasKids As String) As Double
        If Salary > 5000 And Salary < 40000 Then
            If HasKids = "Yes" Then
            getTaxRateElseNestedIF = 0.15
            Else
            getTaxRateElseNestedIF = 0.25
            End If
        ElseIf Salary >= 40000 And Salary < 90000 Then
            If HasKids = "Yes" Then
            getTaxRateElseNestedIF = 0.28
            Else
            getTaxRateElseNestedIF = 0.35
            End If
        ElseIf Salary > 90000 Then
            If HasKids = "Yes" Then
            getTaxRateElseNestedIF = 0.42
            Else
            getTaxRateElseNestedIF = 0.45
            End If
        Else
            If HasKids = "Yes" Then
            getTaxRateElseNestedIF = 0
            Else
            getTaxRateElseNestedIF = 0.01
            End If
        End If
    End Function
    Last edited by Paul_Hossler; 01-18-2018 at 12:19 PM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    You can simplify it a little

    Option Explicit
    Function getTaxRate(Salary As Double, HasKids As String) As Double
        Select Case Salary
            Case Is > 90000
                getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45)
            Case Is >= 40000
                getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35)
            Case Is >= 5000
                getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25)
            Case Else
                getTaxRate = IIf(HasKids = "Yes", 0#, 0.01)
        End Select
    End Function
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Your solution is shorter, but I have the same problem. I have 3,000.00 salary and Yes from column HasKids and I get result (0.01). I do not why. Show me mistake im my code or your code, or try put Yes in cell A1 and 3000 in A2 and try my or your code.

  4. #4
    I tried this simple code to check my problem, but still I get result 0.01...Is it normal? S = 3000, H = Yes. Why do I get 0.01 result?!
    Function test(S As Double, H As String) As Double
        If S > 5000 And S < 40000 Then
            If H = "Yes" Then
            test = 0.15
            Else
            test = 0.25
            End If
        Else
            If H = "Yes" Then
            test = 0
            Else
            test = 0.01
            End If
        End If
    End Function

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Your AND logic is wrong - both pieces (S > 5000, S < 40000) must be true for the if to be True


    Sub drv()
        MsgBox test(3000, "Yes")
    End Sub
    
    
    'S = 3000, H = Yes
    Function test(S As Double, H As String) As Double
        If S > 5000 And S < 40000 Then ' The AND is not true since while 3000 < 40000, 3000 is NOT > 5000
            If H = "Yes" Then
                test = 0.15
            Else
                test = 0.25
            End If
        Else                            ' so it goes to here
            If H = "Yes" Then           ' H is YES, so
                test = 0                ' the answer is 0
            Else
                test = 0.01
            End If
        End If
    End Function

    If 3000 has different rates, then

    Option Explicit 
    Function getTaxRate(Salary As Double, HasKids As String) As Double 
        Select Case Salary 
        Case Is > 90000 
            getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45) 
        Case Is >= 40000 
            getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35) 
        Case Is >= 5000 
            getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25) 
        Case Is >= 3000 
            getTaxRate = IIf(HasKids = "Yes", 0.09, 0.12) 
        Case Else 
            getTaxRate = IIf(HasKids = "Yes", 0#, 0.01) 
        End Select 
    End Function
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Ok, I understand what do you try to say me. S=3000 so S > 5000 is not true, but S < 40000 is true, so it is problem, but why your code above is wrong and give me still result 0.01?
    Option Explicit 
    Function getTaxRate(Salary As Double, HasKids As String) As Double 
        Select Case Salary 
        Case Is > 90000 
            getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45) 
        Case Is >= 40000 
            getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35) 
        Case Is >= 5000 
            getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25) 
        Case Else 
            getTaxRate = IIf(HasKids = "Yes", 0#, 0.01) 
        End Select 
    End Function

  7. #7
    Still give me wrong result. I do not know why. Simple case and big problem. Should we give up?

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by cruznovsky View Post
    Ok, I understand what do you try to say me. S=3000 so S > 5000 is not true, but S < 40000 is true, so it is problem, but why your code above is wrong and give me still result 0.01?

    Option Explicit 
    Function getTaxRate(Salary As Double, HasKids As String) As Double 
        Select Case Salary 
        Case Is > 90000 
            getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45) 
        Case Is >= 40000 
            getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35) 
        Case Is >= 5000 
            getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25) 
        Case Else 
            getTaxRate = IIf(HasKids = "Yes", 0#, 0.01) 
        End Select 
    End Function

    The way Select Case works is to test the 'selector' , Salary in this case

    Is Salary > 90000 ... No, so try next

    Is Salary > 40000 ... No, so try next

    Is Salary > 5000 ... No, so try next


    The 'next' is the Else, so the return is either 0 or .01


    What should be the answer for Salary = 3000 ???

    If 3000 has different rates, then

    Option Explicit 
    Function getTaxRate(Salary As Double, HasKids As String) As Double 
        Select Case Salary 
        Case Is > 90000 
            getTaxRate = IIf(HasKids = "Yes", 0.42, 0.45) 
        Case Is >= 40000 
            getTaxRate = IIf(HasKids = "Yes", 0.28, 0.35) 
        Case Is >= 5000 
            getTaxRate = IIf(HasKids = "Yes", 0.15, 0.25) 
        Case Is >= 3000 
            getTaxRate = IIf(HasKids = "Yes", 0.09, 0.12) 
        Case Else 
            getTaxRate = IIf(HasKids = "Yes", 0#, 0.01) 
        End Select 
    End Function
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by cruznovsky View Post
    Still give me wrong result. I do not know why. Simple case and big problem. Should we give up?
    There's no need to give up. it's just a matter of understanding your requirement.

    Right now, 3000 always gets the 'Else' answer of o or .01 -- what should it get?
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    If Salary = 3000 and Has Kids is "Yes" result should be 0. If Has Kids is "No" result = 0.01. In every code above my message I get result = 0.01 (Should be 0). Please do not talk about theory and test it in excel. Put in A1 "Yes", put in A2 3000 and in A3 use function. It is not normal in order to result be 0.01...I will go crazy if I will not find the solution of this problem.

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Capture.JPG


    Well, they seem to work for me
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    Ok, so please look for my file and check code in cell E5.
    Attached Files Attached Files

  13. #13
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    "Yes " is not the same as "Yes" -- Fix your inputs to remove trailing spaces


    Capture.JPG

    Please do not talk about theory and test it in excel.
    You need / should add some error checking
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  14. #14
    OMG! Thank you so much! Thank you for show me how to check error. In the future I will set break point and I will analyse how to code working. Thank you again! I learned a lot of in this case and you very help me.

Posting Permissions

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