software

Hi,

I am very new to VBA and am attempting to use a public Function without
much luck.

What I need is I have 6 test results in columns C to H and I need a
Public Function to add the scores up make certain divisions and output
a mark e.g Merit for the score they achieved. I have attempted it in
the code below and have chopped and changed thing so much I am now
totally confused.

This is the code...Code:
--------------------

Option Explicit

Public Function Grade(cell As Range) As String

Dim dblTest1 As Double
Dim dblTest2 As Double
Dim dblTest3 As Double
Dim dblTest4 As Double
Dim dblTest5 As Double
Dim dblProject As Double
Dim dblTotal As Double

Do Until quot;$Iquot; amp; ActiveCell.Row = quot;quot;
If quot;$Hquot; amp; ActiveCell.Row = 0 Then
Grade = quot;No Projectquot;
Else
dblTest1 = cell.Offset(0, -6).Value
dblTest2 = cell.Offset(0, -5).Value
dblTest3 = cell.Offset(0, -4).Value
dblTest4 = cell.Offset(0, -3).Value
dblTest5 = cell.Offset(0, -2).Value
dblProject = cell.Offset(0, -1).Value

dblTest1 = (dblTest1 / 20)
dblTest2 = (dblTest2 / 20)
dblTest3 = (dblTest3 / 10)
dblTest4 = (dblTest4 / 10)
dblTest5 = (dblTest5 / 5)
dblProject = (dblProject / 2)

dblTotal = dblTest1 dblTest2 dblTest3 dblTest4 _
dblTest5 dblProject

Select Case dblTotal
Case Is lt; 40
Grade = quot;Not Achievedquot;
Case Is lt; 60
Grade = quot;Passquot;
Case Is lt; 80
Grade = quot;Meritquot;
Case Is lt; 100
Grade = quot;Distinctionquot;
Case Else
Grade = quot;Errorquot;
End Select

End If
Loop
End Function

--------------------Any help anyone can offer would be great!

Thanks

Andy--
Andy123
------------------------------------------------------------------------
Andy123's Profile: www.excelforum.com/member.php...oamp;userid=29886
View this thread: www.excelforum.com/showthread...hreadid=495902At the very least you need to properly identify ranges

Do Until Range(quot;$Iquot; amp; ActiveCell.Row) = quot;quot;
If Range(quot;$Hquot; amp; ActiveCell.Row) = 0 Then

but you really should not be using activecell and hardcoded columns in a
UDF, you should pass them as UDF arguments. Similarly, it is better to
declare the whole range rather than just one cell and offset it, as changes
to any of those cells will trigger a recalc of your UDF.

What exactly are you trying to do with the data in H amp; I?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Andy123quot; gt; wrote in
message ...
gt;
gt; Hi,
gt;
gt; I am very new to VBA and am attempting to use a public Function without
gt; much luck.
gt;
gt; What I need is I have 6 test results in columns C to H and I need a
gt; Public Function to add the scores up make certain divisions and output
gt; a mark e.g Merit for the score they achieved. I have attempted it in
gt; the code below and have chopped and changed thing so much I am now
gt; totally confused.
gt;
gt; This is the code...
gt;
gt;
gt; Code:
gt; --------------------
gt;
gt; Option Explicit
gt;
gt; Public Function Grade(cell As Range) As String
gt;
gt; Dim dblTest1 As Double
gt; Dim dblTest2 As Double
gt; Dim dblTest3 As Double
gt; Dim dblTest4 As Double
gt; Dim dblTest5 As Double
gt; Dim dblProject As Double
gt; Dim dblTotal As Double
gt;
gt; Do Until quot;$Iquot; amp; ActiveCell.Row = quot;quot;
gt; If quot;$Hquot; amp; ActiveCell.Row = 0 Then
gt; Grade = quot;No Projectquot;
gt; Else
gt; dblTest1 = cell.Offset(0, -6).Value
gt; dblTest2 = cell.Offset(0, -5).Value
gt; dblTest3 = cell.Offset(0, -4).Value
gt; dblTest4 = cell.Offset(0, -3).Value
gt; dblTest5 = cell.Offset(0, -2).Value
gt; dblProject = cell.Offset(0, -1).Value
gt;
gt; dblTest1 = (dblTest1 / 20)
gt; dblTest2 = (dblTest2 / 20)
gt; dblTest3 = (dblTest3 / 10)
gt; dblTest4 = (dblTest4 / 10)
gt; dblTest5 = (dblTest5 / 5)
gt; dblProject = (dblProject / 2)
gt;
gt; dblTotal = dblTest1 dblTest2 dblTest3 dblTest4 _
gt; dblTest5 dblProject
gt;
gt; Select Case dblTotal
gt; Case Is lt; 40
gt; Grade = quot;Not Achievedquot;
gt; Case Is lt; 60
gt; Grade = quot;Passquot;
gt; Case Is lt; 80
gt; Grade = quot;Meritquot;
gt; Case Is lt; 100
gt; Grade = quot;Distinctionquot;
gt; Case Else
gt; Grade = quot;Errorquot;
gt; End Select
gt;
gt; End If
gt; Loop
gt; End Function
gt;
gt; --------------------
gt;
gt;
gt; Any help anyone can offer would be great!
gt;
gt; Thanks
gt;
gt; Andy
gt;
gt;
gt; --
gt; Andy123
gt; ------------------------------------------------------------------------
gt; Andy123's Profile:
www.excelforum.com/member.php...oamp;userid=29886
gt; View this thread: www.excelforum.com/showthread...hreadid=495902
gt;

Hi Bob,

I have a spreadsheet that has a student name in column B, in column C
is the first test result, in column D the second test result, column E
has the third test result, F has the fourth, G has the fifth, and H has
the sixth. In column I i would like to add a function that will add up
all the scores and if the overall score is under 40 they failed under
60 they pass and so on. I want it to show the words instead of the
score so I am guessing I need a case statement.

Hope this helps.

Andy--
Andy123
------------------------------------------------------------------------
Andy123's Profile: www.excelforum.com/member.php...oamp;userid=29886
View this thread: www.excelforum.com/showthread...hreadid=495902
Bump. --
Andy123
------------------------------------------------------------------------
Andy123's Profile: www.excelforum.com/member.php...oamp;userid=29886
View this thread: www.excelforum.com/showthread...hreadid=495902