How can I get the MessageBox for the Range I want?

Question

Sub TestResult()
    Dim Score As Integer, Result As String
    Score = Range("A1:A5").Value

    If Score >= 60 Then
        Result = "pass"
    Else
        Result = "fail"
    End If
Range("B1:B5").Value = Result
End Sub

Score = Range("A1:A5").Value This part is the problem. How should I change it to work?

Answer 1

With ActiveSheet.Range("A1:A5")
    .Offset(0, 1).Value = .Parent.Evaluate("=IF(" & .Address() & ">60,""Pass"",""Fail"")")
End With

Answer 2

Formula method

First of all you can do this with a formula in range B1:B5:

=IF(A:A>=60,"pass","fail")

or you can write that formula with VBA

Range("B1:B5").Formula = "=IF(A:A>=60,""pass"",""fail"")"

The advantage of forumlas is that they update automatically every time the scores change. If you do it with VBA (not using a formula) the result will not update automatically.

---

VBA method

If you still want to do it with VBA you need to loop through your data and test each score. Doing it with an array is probably the fastest way with VBA.

Option Explicit

Public Sub TestResult()
    Dim ScoresArr As Variant 'read values into an array
    ScoresArr = Worksheets("Sheet1").Range("A1:A5").Value

    Dim ResultArr As Variant 'create result array with same size
    ReDim ResultArr(1 To UBound(ScoresArr, 1), 1 To UBound(ScoresArr, 2))

    Dim iRow As Long
    For iRow = 1 To UBound(ScoresArr, 1) 'loop through array
        If ScoresArr(iRow, 1) >= 60 Then 'test each score
            ResultArr(iRow, 1) = "pass"
        Else
            ResultArr(iRow, 1) = "fail"
        End If
    Next iRow

    Worksheets("Sheet1").Range("B1:B5").Value = ResultArr 'write results array back to cells
End Sub

If you want to let the user select the score range use a Application.InputBox with Type:=8 as shown below:

Option Explicit

Public Sub TestResult()
    Dim ScoresRange As Variant
    On Error GoTo CANCEL_TEST 'the next line will throw an error if cancel is pressed
    Set ScoresRange = Application.InputBox(Prompt:="Select the scores", Title:="Test Result", Type:=8)
    On Error GoTo 0 'always re-activate error reporting!!!

    If ScoresRange.Columns.Count <> 1 Then 'test if only one column was selected
        MsgBox "Only selection of one column is allowed."
        Exit Sub
    End If

    Dim ScoresArr As Variant 'read values into an array
    ScoresArr = ScoresRange.Value

    Dim ResultArr As Variant 'create result array with same size
    ReDim ResultArr(1 To UBound(ScoresArr, 1), 1 To UBound(ScoresArr, 2))

    Dim iRow As Long
    For iRow = 1 To UBound(ScoresArr, 1) 'loop through array
        If ScoresArr(iRow, 1) >= 60 Then 'test each score
            ResultArr(iRow, 1) = "pass"
        Else
            ResultArr(iRow, 1) = "fail"
        End If
    Next iRow

    ScoresRange.Offset(ColumnOffset:=1).Value = ResultArr 'write results array back to cells
CANCEL_TEST:
End Sub

Answer 3

For this answer i use Sheet1. If i understand correct you want to put next to each result if it is pass or fail?

Try:

    Option Explicit

Sub TestResult()

    Dim ScoreList As Range, Score As Range, Result As String

    With ThisWorkbook.Worksheets("Sheet1")

        Set ScoreList = .Range("A1:A5")

        For Each Score In ScoreList

            If Score.Value >= 60 Then
                Score.Offset(0, 1).Value = "Pass"
            Else
                Score.Offset(0, 1).Value = "Fail"
            End If

        Next

    End With

End Sub