Excel macro help

[clint86]

I need help with an excel macro that will take a username (string) in the first column and look at everytime that they either have an P (present), T (tardy), E (Excused), or A (absent) in a second column. They will be given 400 points if they were present every time, but for every T (10 points), A (60 points), E (25 points) will be deducted from their original 400 point score. Any help would be aprreciated, I don't know where to start.

[Stewart Ross]

Hi. This would appear to be a homework question, and much as we would like to help you we cannot do your homework for you. If there is something specific on which you are stuck and need help then please ask for assistance on that - but we simply cannot give you much in the way of help with your homework question when you have put no effort at all into problem solving for yourself.

Please refer to our guidance on Posting Homework Questions in our FAQ reached from the Posting Guidelines at the head of the forum.

MODERATOR

[clint86]

Hi. This would appear to be a homework question, and much as we would like to help you we cannot do your homework for you. If there is something specific on which you are stuck and need help then please ask for assistance on that - but we simply cannot give you much in the way of help with your homework question when you have put no effort at all into problem solving for yourself.

Please refer to our guidance on Posting Homework Questions in our FAQ reached from the Posting Guidelines at the head of the forum.

MODERATOR

This is actually not a homework problem, since I am not in school, but an assistant teacher asks for my help and we are clueless on how to do this, though I took a class in Visual Basic 3 years ago I do not remember it that well and never incoporated it with an office software. So if no help is given I will not cry about it, but I just wanted to help a friend. Also I am not asking for anyone to do this for me, but help point me in the right direction.

But I wanted to add that in excel I'm using a successful fomula and it is . . .
=SUMPRODUCT(COUNTIFS(B:B,G2,E:E,{"A";"E";"T"}),{60 ;25;10})
This then will be subtracted from 400, but I need a macro. Please, any help would be appreciated.

[Stewart Ross]

I remain uncomfortable with this one - it is possible that a solution to this class problem will undermine the efforts of students who may also be set this problem at some stage. I will provide one possible function which can calculate the demerit total, but otherwise I must leave it to you to do the rest of the work involved in linking this into your worksheet.

Public Function fTotalDemerits(FirstRow as Long, LastRow as Long, ColumnNo as Long) as Long
 
Dim lngTotal as Long
Dim lngRowCounter as Long
Dim strMarker as String
 
For lngRowCounter = FirstRow to LastRow
   strMarker = activesheet.Cells(lngRowCounter, ColumnNo)
   Select Case strMarker
       Case "A"
         lngTotal = lngTotal + 60
       Case "E"
         lngTotal = lngTotal + 25
       Case "T"
         lngTotal = lngTotal + 10
   End Select
Next lngRowCounter
fTotalDemerits = lngTotal
End Function

The function itself does total the points for cells in the specified column of the activesheet between the specified first and last rows. So, if your markers are in column E between rows 1 and 20 the function's arguments would be 1, 20, 5 (for rows 1 to 20 and column 5 (E)) respectively.

Your task then is to suitably call the function (or write another to do the job), providing the necessary values for the arguments, and place the result of the function back into a cell on the worksheet where you can see it.

-Stewart