Results 1 to 15 of 29

20040115, 16:02 #1
 Join Date
 Jan 2001
 Location
 Jacksonville,NC, USA
 Posts
 705
 Thanks
 0
 Thanked 0 Times in 0 Posts
Excel Problem in VBA Code or Formula (Excel)
I have created a CASE function called tax for State $ due, it is as follows: This formula also depends on whether the person is married or single which I have put into the IF Function in this cell to determine if on the Employee Data Sheet cell D6 says "M" or "S" for single or married, which should pull one or the other function into play that I have written in the file, press F11 to see the code...either statetax or statetax 2
MY PROBLEM: I get the same answer whether D6 on the Employee Sheet says "M" or "S" Can you tell me whats wrong?NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
If you can't convince them, confuse them.  Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20040115, 16:28 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
This is a simpler formula:
=IF('Employee Data'!D6="M",statetax(K4)*K4,statetax2(K4)*K4)
For your functions use (eg)
Case Is = 0 <= 250
is incorrect syntax (so you are not grabbing the correct rate)
Use:
Case is <=250
or
Case 0 To 250
or
Case Is >= 0 and STATE <= 250
You could do this with a lookup table instead of coding
Steve

20040115, 16:29 #3
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
Your Case Statements are not properly set out, Try
Case Is = 0, Is <= 250
etc.Grüße

20040115, 16:46 #4
 Join Date
 Jan 2001
 Location
 Jacksonville,NC, USA
 Posts
 705
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
The formula you gave me is doing the same thing my formula is doing...it's shorter thats a good thing, but I still get the same answer whether its' M or S in cell D6 on the Employee Data sheet.
I fixed the code CASE statement like you said...
Could you send me the file back with your change and let me see what I am not doing right?NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
If you can't convince them, confuse them.  Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20040115, 16:54 #5
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
I have tested it with my change and it seems to give the right answers  see attached.
Grüße

20040115, 17:03 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
Here is a correction
Steve
<pre>Public Function statetax(State As Single)
Select Case State
Case Is <= 250
statetax = 0
Case Is <= 1312
statetax = 0.06
Case Is <= 2540
statetax = 0.07
Case Is < 4365.8
statetax = 0.08
Case Else
statetax = 0.09
End Select
End Function
Public Function statetax2(State As Single)
Select Case State
Case Is <= 366.67
statetax2 = 0
Case Is <= 1783.33
statetax2 = 0.06
Case Is <= 2760
statetax2 = 0.07
Case Is <= 4830.25
statetax2 = 0.08
Case Else
statetax2 = 0.09
End Select
End Function</pre>

20040115, 17:08 #7
 Join Date
 Jan 2001
 Location
 Jacksonville,NC, USA
 Posts
 705
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
I am still getting the same answer in your worksheet of 1248.00 whether it is M or S in the Marital Status cell of D6 on the Employee Data Sheet, aren't you? Did you try placing M, then S and seeing if you come up with a different rate?
NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
If you can't convince them, confuse them.  Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20040115, 17:14 #8
 Join Date
 Jan 2001
 Location
 Jacksonville,NC, USA
 Posts
 705
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
With the Code you gave me I get 1872 for an answer, and still does not matter if M is in D2 or S, the figure doesn't change accordingly.
<img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
If you can't convince them, confuse them.  Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20040115, 17:18 #9
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
Part of the issue seems to be that the values in your ranges overlap. To keep it simple, I ran it at 300 and got 18 in one case and 0 in the other. HTH
Grüße

20040115, 17:27 #10
 Join Date
 Jan 2001
 Location
 Jacksonville,NC, USA
 Posts
 705
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
My Code overlaps how?
Public Function statetax(State As Single)
Select Case State
Case Is = 0, Is <= 250
statetax = 0
Case Is > 250, Is <= 1312
statetax = 0.06
Case Is > 1312, Is <= 2540
statetax = 0.07
Case Is > 2540, Is <= 4365.8
statetax = 0.08
Case Is > 4365.8
statetax = 0.09
End Select
End Function
Public Function statetax2(State As Single)
Select Case State
Case Is = 0, Is <= 366.67
statetax2 = 0
Case Is > 366.67, Is <= 1783.33
statetax2 = 0.06
Case Is > 1783.33, Is <= 2760
statetax2 = 0.07
Case Is > 2760, Is <= 4830.25
statetax2 = 0.08
Case Is > 4830.25
statetax2 = 0.09
End Select
I don't see where? I have to use decimal places if thats what you mean, and even taking those out of the above and rounding the numbers had no effect. I have used my original formula of:
=IF('Employee Data'!D6="M",statetax(K4)*K4,IF('Employee Data'!D6="S",statetax2(K4)*K4))
and the formula given to me above that was supposed to take some of the weight off, but still the same answer every time no matter what I put in D6 on the Employee Data sheet. Any other clues on this?NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
If you can't convince them, confuse them.  Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20040115, 17:48 #11
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
When I said "ranges", I meant ranges of values. If both are <250, then both are 0. If both are >4830.25, both are 0.09. That's why I tested with 300; as  since it is between 250 and 366.67 (the two threshold values)  it should produce separate values.
Grüße

20040115, 18:13 #12
 Join Date
 Jan 2001
 Location
 Jacksonville,NC, USA
 Posts
 705
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
Makes sense.
Just don't know what I'm to do about it. If they are so close they are overlapping then the answer would be the same in both instances. The part I'm unclear about is, I am calling to different functions, it seems like there would be a slight change if only in cents.
I've let this one drive me over the edge. HansV I know you said you'd check this for me later...would you still give me any and all advice on this, and anyone else who might want to try...thank you very much unkamunka...your patience has been tried, and I am appreciative of that...truly.NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
If you can't convince them, confuse them.  Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20040115, 18:28 #13
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
Use Steve's code. It produces cleaner results. I get changes between the two situations at 300, 1700 and 4400.
A data question: with the formula you are using, the State Tax liability is higher for Married people than Singles. Is that the right way around?
HTHGrüße

20040115, 18:52 #14
 Join Date
 Jan 2001
 Location
 Jacksonville,NC, USA
 Posts
 705
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
I did switch to Steve's code, and my answer is $1778.50 different from the first code, but still the same whether its M or S in D6 still. These are the figures as given me, and was told there may be intentional errors included to see if I can find them, that could be the case in why married are getting taxed more then single..and no, I don't know if that's the way its supposed to be or not..I'm no payroll clerk (by a long shot)...anyhow...have a start...however, there's more to it...I still have to subtract dependents and the 36.67 per dependent deduction from this answer, and subtract 15 or 28.13 or 41.11 or 49.42 from that answer depending on what percentage they are being taxed by. Just trying to get the first part of this formula down before I tackle any more of it.
NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>
If you can't convince them, confuse them.  Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

20040115, 18:59 #15
 Join Date
 Nov 2001
 Location
 Vienna, Wien, Austria
 Posts
 5,009
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Excel Problem in VBA Code or Formula (Excel)
At the three values I mention, changes in D6 do change. You are using Steve's code from the post that starts "Here is a correction"?
Thanks for the clarification on the data. It was just the old <img src=/S/doh.gif border=0 alt=doh width=15 height=15>"common sense" test that I usually apply.Grüße