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 :)

Code:

---

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

---

You can simplify it a little

Code:

---

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

---

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.

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?!

Code:

---

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

---

Your AND logic is wrong - both pieces (S > 5000, S < 40000) must be true for the if to be True

Code:

---

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

Code:

---

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

---

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?

Code:

---

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

---

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

Quote:

---

Originally Posted by cruznovsky

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?

Code:

---

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

Code:

---

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

---

Quote:

---

Originally Posted by cruznovsky

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?

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.

Attachment 21390


Well, they seem to work for me

Ok, so please look for my file and check code in cell E5.

"Yes " is not the same as "Yes" -- Fix your inputs to remove trailing spaces


Attachment 21393

Quote:

---

Please do not talk about theory and test it in excel.

---

You need / should add some error checking

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.