carbon_13,
Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).
Press and hold down the 'ALT' key, and press the 'F11' key.
Insert a Module in your VBAProject, Microsoft Excel Objects
Copy the below code, and paste it into the Module1.
Code:
Option Explicit
Function SumColumns(r As Range) As Long
'
' Function created 03/04/2008 by stanleydgromjr
'
' for carbon_13
' http://p2p.wrox.com/post.asp?method=...90&FORUM_ID=72
'
' Formula in cell F1 (copied down):
' =SumColumns($A1:$E1)
'
'
Dim c
Dim lngLoopCtr As Long
Dim lngTotal As Long
lngTotal = 0
lngLoopCtr = 1
For Each c In r.Cells
Select Case lngLoopCtr
'value of the letter grades are A = 4, B = 3, C = 2, D = 1, F = 0
Case 1, 3, 4, 5
Select Case c
Case "A"
lngTotal = lngTotal + 4
lngLoopCtr = lngLoopCtr + 1
Case "B"
lngTotal = lngTotal + 3
lngLoopCtr = lngLoopCtr + 1
Case "C"
lngTotal = lngTotal + 2
lngLoopCtr = lngLoopCtr + 1
Case "D"
lngTotal = lngTotal + 1
lngLoopCtr = lngLoopCtr + 1
Case "F"
lngTotal = lngTotal + 0
lngLoopCtr = lngLoopCtr + 1
End Select
'column 2 had different values for the letter grades such as (A = 12, B = 9, C = 6, D = 3, F = 0)
Case 2
Select Case c
Case "A"
lngTotal = lngTotal + 12
lngLoopCtr = lngLoopCtr + 1
Case "B"
lngTotal = lngTotal + 9
lngLoopCtr = lngLoopCtr + 1
Case "C"
lngTotal = lngTotal + 6
lngLoopCtr = lngLoopCtr + 1
Case "D"
lngTotal = lngTotal + 3
lngLoopCtr = lngLoopCtr + 1
Case "F"
lngTotal = lngTotal + 0
lngLoopCtr = lngLoopCtr + 1
End Select
End Select
Next c
SumColumns = lngTotal
End Function
With your data beginning in row 1, cell A1, enter the following formula in cell F1 (then copy it down):
=SumColumns($A1:$E1)
Have a great day,
Stan
stanleydgromjr
Windows Vista Business and Excel 2003, 2007.
VBA function help
