Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    23

    Exclamation Unanswered: VBA Code For Macro? III

    Last Piece: I think.

    Here is my final part:
    I have :
    MON TUES WED THUR FRI Total Sick
    1-Jun 2-Jun 3-Jun 4-Jun 5-Jun

    8 8 8 8 8 40 0

    These field Lead to a CALCULED ( SUM ) on the right.

    However, I want the Value to allow Alpha Numerics and STILL calculate:
    Example:

    MON TUES WED THUR FRI
    1-Jun 2-Jun 3-Jun 4-Jun 5-Jun

    I3 I4 8 8 8

    The above shows 3 Hours of Sick Time on Monday
    The above shows 4 Hours of Sick Time on Tuesday

    I still Need the Total to take what WOULD be in that Cell ( 8 hours) Less the Alpha Numeric that is in there so the Returned TOTAL would be 40-3-4=33

    So the END result:

    MON TUES WED THUR FRI Total Sick
    1-Jun 2-Jun 3-Jun 4-Jun 5-Jun

    I3 I4 8 8 8 33 7

    That is ONLY if they USE I ( code ) I = Sick A= Vacation so - I need the code that will dump hose figures into the correct Buckets..Please help !

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi

    I would do this by a function

    in the Absence bucket put something like
    =SUMPRODUCT(--(LEFT($A3:$E3,1) = "I"),VALUE(RIGHT($A3:$E3,1)))

    I presumed the monday to friday data was located in the columns A to E

    in the Vacation bucket put

    =SUMPRODUCT(--(LEFT($A3:$E3,1) = "A"),VALUE(RIGHT($A3:$E3,1)))

    and in your total line put

    40 - sick bucket - vacation bucket

    HTH

    David

Posting Permissions

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