VBA: run time error 91

[jacquesB]

Hi guys

I am trying to run the code below but I get a run time error 91 stating: "Object variable or With block variable not set".

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Intersect(Target, Range("O25:O50")).Value = 2 Then
        MsgBox "This is a warning message", vbOKOnly & vbCritical, "Wrong Data Entry"
    End If
    If Not Intersect(Target, Range("G8")) Is Nothing Then
        Q = MsgBox("Do you want end session?", vbQuestion + vbYesNo, "OPTION")
        If Q = vbYes Then
            Sh.Protect "Carlsberg" 'Substitute actual password here
        End If
    End If

End Sub

When I use the debugger it says that the error lies in the second line of code. I.e.

If Intersect(Target, Range("O25:O50")).Value = 2 Then

I hope that someone on this forum can see a way to fix it as it is way beyond my VBA knowledge.

Thank you!

 

[Joe4]

That is not valid syntax.
What exactly are you trying to do with this line?

If Intersect(Target, Range("O25:O50")).Value = 2 Then

Since the intersection of two ranges returns another range, which could be multiple cells, it doesn't really make sense to ask if that range is equal to 2 (since there could be more than one cell in that range).

Are you trying to count the number of cells in that intersected range? If so, use .Count instead of .Value.
If you want to see if any cells in that intersected range equal 2, I would loop through each cell in the range and check them individually.

 

[jacquesB]

I am trying to do the latter and see if any cell contain the value 2.

How do you make the loop? I am sorry for my missing expertise

 

[Joe4]

That block might look something like this:

    Dim myRange As Range
    Dim cell As Range
    
    Set myRange = Intersect(Target, Range("O25:O50"))
    If myRange.Count > 0 Then
        For Each cell In myRange
            If cell.Value = 2 Then MsgBox "This is a warning message", vbOKOnly & vbCritical, "Wrong Data Entry"
        Next cell
    End If