Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Public Function Problems
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 "$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 Andy -- Andy123 ------------------------------------------------------------------------ Andy123's Profile: http://www.excelforum.com/member.php...o&userid=29886 View this thread: http://www.excelforum.com/showthread...hreadid=495902 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Public Function Problems
At the very least you need to properly identify ranges
Do Until Range("$I" & ActiveCell.Row) = "" If Range("$H" & 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 & I? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Andy123" wrote in message ... 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 "$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 Andy -- Andy123 ------------------------------------------------------------------------ Andy123's Profile: http://www.excelforum.com/member.php...o&userid=29886 View this thread: http://www.excelforum.com/showthread...hreadid=495902 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Public Function Problems
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: http://www.excelforum.com/member.php...o&userid=29886 View this thread: http://www.excelforum.com/showthread...hreadid=495902 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Public Function Problems
Bump. :) :) -- Andy123 ------------------------------------------------------------------------ Andy123's Profile: http://www.excelforum.com/member.php...o&userid=29886 View this thread: http://www.excelforum.com/showthread...hreadid=495902 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problems with RATE function returning #NUM | Excel Worksheet Functions | |||
Public Function to Return ColorIndex | Excel Discussion (Misc queries) | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
opening up an english excel sheet onto a french verions - problems with edate function | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |