Whats wrong with this macro code???

Question

Can someone help me with my code? It keeps showing Pass and Merit for every name and option button i select.


Option Explicit
Dim Average_Mark As Double

Private Sub CmdOK1_Click()
If FrmMark.OptModule1 = True Then
Range("Data!c26").Value = 5
End If
If FrmMark.OptModule2 = True Then
Range("Data!c26").Value = 9
End If
If FrmMark.OptModule3 = True Then
Range("Data!c26").Value = 13
End If

Average_Mark = Range("Data!c28").Value

If Average_Mark <= 40 Then
MsgBox "Fail"
End If
If Average_Mark > 40 < 55 Then
MsgBox "Pass"
End If
If Average_Mark >= 55 < 70 Then
MsgBox "Merit"
End If
If Average_Mark >= 70 Then
MsgBox "Distinction"
End If

End Sub

Ok thanks guys i got that part to work but i still have one problem.

On the form i have a list of names, but the message keeps displaying the message for one name.

I want it so i can click on a name and it show the answers for the person.....

...If im getting too complicated dont worry. Thanks.

Answer 1

I think your second set of if statements is set up wrong. Mainly for the Pass and Merit conditions. I think it should look like:
If Average_Mark <= 40 Then
...
ElseIf Average_Mark > 40 And Average Mark < 55 Then
...
ElseIf Average_Mark >= 55 And Average_Mark < 70 Then
...
ElseIf Average_Mark >= 70 Then
...
End If

And to answer the question above me, it is VBA (Visual Basic for Applications) The language Excel uses to creates its macros.

Answer 2

Correct me if I'm wrong but the first half of the procedure has nothing to do with the second half. The first half sets the value of cell C26; however, when you set the value of Average_Mark it refers to cell C28.

I think that's your problem.

Also instead of using multiple If statements, use a Select Case Statement.

Select Case Average_Mark
Case <=40
MsgBox "Fail"
Case <55
MsgBox "Pass"
Case <70
MsgBox "Merit"
Case Else
MsgBox "Distinction"
End Select

This statement does exactly what you had but it much cleaner and more efficient as the code doesn't have to go thru each if statement that you have.

Now I admit, I get confused as to which one is greater than or lesser than. So it is entirely possible you need to switch them.

Answer 3

Your syntax for these two subs is wrong
If Average_Mark > 40 < 55 Then
MsgBox "Pass"
End If
If Average_Mark >= 55 < 70 Then
MsgBox "Merit"
End If

they should be:
If Average_Mark > 40 and Average_Mark < 55 Then

and

If Average_Mark >54 and Average_Mark < 70 Then

Looking at your code this should resolve your issue...

Answer 4

What mike said!!

Not sure if it works in VBA.. but in VB you can use a case statment.

Example:


Select case Average_Mark
case <= 40
Msgbox("fail")
case 41 to 54
Msgbox("pass")
case 55 to 69
msgbox....
Case else
msgbox...
End select

Answer 5

What language is that