# Thread: VBA Code For Macro? III

1. Registered User
Join Date
Jun 2004
Posts
23

## 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. Registered User
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
•