Right function Count

[scott1971]

Guys,

I've been trying to work out a problem on XL and had a thread going on the Excel Forum but I've been pointed towards you guys as a possible help with this.

My post is below and below that is the post stating what could possibly be done. However, I haven't used VBA before so I'd almost need an idiots guide to get this to work I think.

MY POST - Worksheet Data:

Name 1st 2nd 3rd Total Holidays Total Absence
Scott 7.5 S 3.75 11.25 1*

The Users select the hours of holidays they have had and I sum that quite simply with = SUM (B2:AF2). The Absence is defined with an S and I Count that using =COUNTIF(B2:AF2,"S"). From the Users point of view they have a "pick list" with all the Holiday hours available and the font and background are the same colour (blue) so as they can easily identify the holidays. Same with sick days. just now though Sick days are counted simply as days and it works fine but they now want to count it by hours(though they'd be happy not to count 1/4 or 1/2 hours).


REPLY - Also you could use a userdefined function in vba (I Know this is not the vba forum) and then put in sick days as "S, 4" in a cell then use a vba function to run through col b - af and if any cell starts with an s then use the right function to get the hours and sum up. If you want to pursue this please post in vba forum

Any help would be fantastic.

Cheers

Scott

 

[xlbo]

Function GetSickDays(rng as range)
dim c as cell, hrs as long
hrs = 0
for each c in rng
 if left(c,1) = "S" then
   hrs = hrs + right(c,len(c)-3)
 end if
next
GetSickDays = hrs
End Function

I still say that you are making a rod for your own back though. If you go down this route you will need to support the vba. Then someone will ask for more functionality and because this is the route you have chosen, you will have to make it more complicated. Continue ad nauseum until you are sick and tired off it and wishing you had taken the time initially to set it up in the right way to start with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question