Excel VBA help please

[IntoTheVoid]

Hi,

I am just starting with vba in Excel 2010 and have done some .net Visual Basic programming. However I am finding it hard going with vba. I am trying to run the following, but keep getting errors when trying to run. My worksheet is called model and is the first sheet in the workbook:

Sub workoutgrade()

Dim grade As String



If Range("pass4") > 7 And Range("pass5") > 7 Then
grade = "Pass"
ElseIf Range("merit4") > 4 And Range("merit5") > 5 Then
grade = "Merit"
ElseIf Range("dist4") > 4 And Range("dist5") > 5 Then
grade = "Distinction"
ElseIf Range("pass4") + Range("pass5") < 16 And grade <> "Merit" Or "Distinction" Then
grade = "Fail"
Else: grade = "Merit"
Range("grade") = grade
End If




End Sub

the cells referred to by range hold a numerical value. I want to run the conditional tests and output the grade string to named range cell grade. Any ideas would be appreciated. I think I have probably got the syntax wrong!!!!

 

[Zhokel]

Sub workoutgrade()

Dim grade As String

If Range("pass4") > 7 And Range("pass5") > 7 Then
    grade = "Pass"
ElseIf Range("merit4") > 4 And Range("merit5") > 5 Then
    grade = "Merit"
ElseIf Range("dist4") > 4 And Range("dist5") > 5 Then
    grade = "Distinction"
ElseIf Range("pass4") + Range("pass5") < 16 Then
    grade = "Fail"
Else
    grade = "Merit"
End If

Range("grade") = grade

End Sub

So this works for me.

And grade <> "Merit" Or "Distinction" Then

This part was giving me a type mismatch, don't think that is how you compare strings in VB? Or at least VBA? Either way I removed it because it's not needed, the only way grade will be Merit or Distinction is if either the previous two ElseIf's were true, which would then skip the rest of the checks and hit the End If. The string would be "" or empty when it checks against these so if it did work then this would always be true.

Also I don't think it should be "Else:"? I don't know personally but again I removed it heh. And I then moved

Range("grade") = grade

to outside of the If statements so that it is always hit after exiting the If's Before it was hitting if all the other checks failed and entered the "Else" statement, so the cell would only update in that scenario.

I've not done VB or VBA in over 6 years? So someone may improve it or answer the questions I had xD, but I think that covers most of it and gets it working

 

[dazzerd]

You probably want to be more specific with your range. I'm guessing you have defined them as named ranges in the workbook. A range can have lots of properties, such as interior color or width etc etc. Specify .value after range, i.e.

Range("something").value, it is best practice.

You also don't really the colon after the else. For clarity I would also ensure that your dimmed string grade has a different variable name to the named range grade. Other than that your logic seems messed up in your 4th elseIf statement grade will never be anything if it reaches that point so I don't know why you are testing it.

What you probably want to be finishing the first If block on the 3rd statement and then have another if block to test what grade is.

Something like this...

Dim strGrade as string

If Range("pass4").value > 7 And Range("pass5"),value > 7 Then
strGrade = "Pass"
ElseIf Range("merit4").value > 4 And Range("merit5").value > 5 Then
strGrade = "Merit"
ElseIf Range("dist4").value > 4 And Range("dist5").value > 5 Then
strGrade = "Distinction"
Else
strGrade = "" ' you dont actually need this but it wll return a blank if none of the conditions above are met
End If

If Range("pass4").value + Range("pass5").value < 16 And (strGrade <> "Merit" Or strGrade <> "Distinction") Then
strGrade = "Fail"
Else
strGrade = "Merit"
Range("grade").value = strGrade
End If

 

[IntoTheVoid]

Hi, thanks for the replies. I ended up with:

Private Sub grades()

Dim grade As String
Dim pass4 As Integer
Dim pass5 As Integer
Dim merit4 As Integer
Dim merit5 As Integer
Dim dist4 As Integer
Dim dist5 As Integer
Dim fail As Integer

pass4 = Range("pass4").Value
pass5 = Range("pass5").Value
merit4 = Range("merit4").Value
merit5 = Range("merit5").Value
dist4 = Range("dist4").Value
dist5 = Range("dist5").Value
failcheck = Range("failcheck").Value


If pass4 > 5 And pass5 > 8 Then
grade = "Pass"

ElseIf merit4 > 4 And merit5 > 5 Then
grade = "Merit"

ElseIf dist4 > 4 And dist5 > 5 Then
grade = "Distinction"

ElseIf merit4 + dist4 > 4 And merit5 + dist5 > 5 And dist4 < 5 And dist5 < 6 Then
grade = "Merit"

ElseIf merit4 < 5 And merit5 < 6 And dist4 < 5 And dist5 < 6 And Range("failcheck").Value > 0 Then
grade = "Fail"

Else: grade = "Pass"

End If

Range("grade").Value = grade

End Sub

This seems to work. Cheers

 

[IntoTheVoid]

thanks also for the advice on making sure the variable name is different than the named range, did not spot that.