A
Andy123
Hi,
I am very new to VBA and am attempting to use a public Function withou
much luck.
What I need is I have 6 test results in columns C to H and I need
Public Function to add the scores up make certain divisions and outpu
a mark e.g Merit for the score they achieved. I have attempted it i
the code below and have chopped and changed thing so much I am no
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 "$I" & ActiveCell.Row = ""
If "$H" & ActiveCell.Row = 0 Then
Grade = "No Project"
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 < 40
Grade = "Not Achieved"
Case Is < 60
Grade = "Pass"
Case Is < 80
Grade = "Merit"
Case Is < 100
Grade = "Distinction"
Case Else
Grade = "Error"
End Select
End If
Loop
End Function
-------------------
Any help anyone can offer would be great!
Thanks
And
I am very new to VBA and am attempting to use a public Function withou
much luck.
What I need is I have 6 test results in columns C to H and I need
Public Function to add the scores up make certain divisions and outpu
a mark e.g Merit for the score they achieved. I have attempted it i
the code below and have chopped and changed thing so much I am no
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 "$I" & ActiveCell.Row = ""
If "$H" & ActiveCell.Row = 0 Then
Grade = "No Project"
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 < 40
Grade = "Not Achieved"
Case Is < 60
Grade = "Pass"
Case Is < 80
Grade = "Merit"
Case Is < 100
Grade = "Distinction"
Case Else
Grade = "Error"
End Select
End If
Loop
End Function
-------------------
Any help anyone can offer would be great!
Thanks
And