More than 1 score

[Plastik808]

Hi Everyone...i am new to VBA and am stuck on a problem. I am trying to expand the following so i can have say 10 people having taken a test and require the code to tell me if they pass or fail. I have tried using score = Range("A1:A10").Value, but i got an error

Private Sub CommandButton1_Click()
Dim score As Integer, grade As String
score = Range("A1").Value
If score >= 60 Then
grade = "passed"
Else
grade = "failed"
End If
Range("B1").Value = grade
End Sub


Many thanks

 

[cstimart]

Try this...

Private Sub CommandButton1_Click()
Dim grade(1 to 10) As String
Dim i as Integer
For i = 1 to 10
    grade(i) = IIF(Range("A" & i) >= 60, "passed", "failed")
Next i
End Sub

You might actually need to declare the grade array as a Public variable, so it is accessible in your Module.

 

[Peter_SSs]

Welcome to the MrExcel board!

Do you really need a Command Button and code? Could you just use a worksheet formula like this, copied down?

Excel Workbook
	A	B
1	Score	Grade
2	8	failed
3	95	passed
4
5	60	passed
6	122	passed
7	80	passed
8	59.5	failed
9
10	66	passed
Grade

 

[Peter_SSs]

Try this...

Private Sub CommandButton1_Click()
Dim grade(1 to 10) As String
Dim i as Integer
For i = 1 to 10
    grade(i) = IIF(Range("A" & i) >= 60, "passed", "failed")
Next i
End Sub

Wouldn't you need to write the array values back to the sheet?

 

[cstimart]

Wouldn't you need to write the array values back to the sheet?

My bad

Private Sub CommandButton1_Click() 
Dim grade(1 to 10) As String 
Dim i as Integer 
    For i = 1 to 10     
        Range("B" & i) = IIF(Range("A" & i) >= 60, "passed", "failed")
    Next i 
End Sub

 

[Plastik808]

yes can easily be done in excel itself, but wnated something to practise with in VBA