By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,497 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,497 IT Pros & Developers. It's quick & easy.

Excel macro help

P: 2
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.
Nov 3 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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
Nov 3 '08 #2

P: 2
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.
Nov 3 '08 #3

Expert Mod 2.5K+
P: 2,545
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function fTotalDemerits(FirstRow as Long, LastRow as Long, ColumnNo as Long) as Long
  2.  
  3. Dim lngTotal as Long
  4. Dim lngRowCounter as Long
  5. Dim strMarker as String
  6.  
  7. For lngRowCounter = FirstRow to LastRow
  8.    strMarker = activesheet.Cells(lngRowCounter, ColumnNo)
  9.    Select Case strMarker
  10.        Case "A"
  11.          lngTotal = lngTotal + 60
  12.        Case "E"
  13.          lngTotal = lngTotal + 25
  14.        Case "T"
  15.          lngTotal = lngTotal + 10
  16.    End Select
  17. Next lngRowCounter
  18. fTotalDemerits = lngTotal
  19. 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
Nov 4 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.