Here's some code to perform some basic maths on exam marks as per my previous 2 posts. Note that it will work with values split with separators (such as "|") in the case of answers to multiple questions.
I could make a UserForm to make it pretty, but for now Input Boxes will do the trick just as well. (You might like to run this at the same time as my highlighting code if you want to see the changes. You can always change back to a normal font format later)
Sub Test()
Dim rCell As Range
Dim rRange As Range
Dim sSeparatorToUse As String
Dim sOperatorToUse As String
Dim dInputValue As Double
If TypeName(Selection) <> "Range" Then Exit Sub
sSeparatorToUse = InputBox("Input an separator")
If sSeparatorToUse = "" Then Exit Sub
sOperatorToUse = InputBox("Input an operator ( + - * / ) ")
If sOperatorToUse = "" Then Exit Sub
dInputValue = InputBox("Input a value")
If Not IsNumeric(dInputValue) Then Exit Sub
Set rRange = Selection
For Each rCell In rRange.Cells
Call PerfomMathOnSplitCellValues(rCell, sSeparatorToUse, sOperatorToUse, dInputValue)
Next rCell
Set rRange = Nothing
End Sub
Sub PerfomMathOnSplitCellValues(rCellMath As Range, sSeparator As String, sOperator As String, dValue As Double)
Dim tmp As Variant
Dim lCount As Long
Dim sTmpString As String
On Error Resume Next
With rCellMath
tmp = Split(.Text, sSeparator)
For lCount = 0 To UBound(tmp)
If lCount = 0 Then
sTmpString = ""
Select Case sOperator
Case Is = "+"
sTmpString = CStr(Val(tmp(lCount)) + dValue)
Case Is = "-"
sTmpString = CStr(Val(tmp(lCount)) - dValue)
Case Is = "*"
sTmpString = CStr(Val(tmp(lCount)) * dValue)
Case Is = "?"
sTmpString = CStr(Val(tmp(lCount)) / dValue)
End Select
Else
Select Case sOperator
Case Is = "+"
sTmpString = sTmpString & sSeparator & CStr(Val(tmp(lCount)) + dValue)
Case Is = "-"
sTmpString = sTmpString & sSeparator & CStr(Val(tmp(lCount)) - dValue)
Case Is = "*"
sTmpString = sTmpString & sSeparator & CStr(Val(tmp(lCount)) * dValue)
Case Is = "/"
sTmpString = sTmpString & sSeparator & CStr(Val(tmp(lCount)) / dValue)
End Select
End If
Next lCount
rCellMath.Value = sTmpString
End With
On Error GoTo 0
End Sub
Some useful (?) stuff and maybe unusual (?) stuff coming up. Give me a week or so.
See you next time.