Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    5 Star Lounger
    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>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    Platinum Lounger
    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.
    Gre

  4. #4
    5 Star Lounger
    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>

  5. #5
    Platinum Lounger
    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.
    Gre

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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>


  7. #7
    5 Star Lounger
    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>

  8. #8
    5 Star Lounger
    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>

  9. #9
    Platinum Lounger
    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
    Gre

  10. #10
    5 Star Lounger
    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>

  11. #11
    Platinum Lounger
    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.
    Gre

  12. #12
    5 Star Lounger
    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>

  13. #13
    Platinum Lounger
    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?

    HTH
    Gre

  14. #14
    5 Star Lounger
    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>

  15. #15
    Platinum Lounger
    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.
    Gre

Page 1 of 2 12 LastLast

Posting Permissions

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