Microsoft Office Forums - View Single Post - Object Variable or With block variable not set

gmayor · 01-07-2015, 03:48 AM

You are ascribing a single value to a range that comprises multiple locations.
Also you have assigned a variable name to the range and then didn't use it? However this will not affect the result.

If any of the values in the range is a below 50 then the following will indicate 'fail'.

Code:

Private Sub CommandButton21_Click()
Dim marks As Range
Dim bFail As Boolean
Dim n As Integer
    Set marks = Range(Cells(1, 1), Cells(4, 1))
    For n = 1 To marks.Rows.Count
        If marks.Cells(n, 1) < 50 Then
            bFail = True
            Exit For
        End If
    Next n
    If bFail Then
        Range("A6") = "Fail"
    Else
        Range("A6") = "Pass"
    End If
End Sub