VBA Runtime Error 424 Object Required - I think I must just be stupid

[IndyBob]

I keep getting an error and don't really know what it means or how to fix it. I have spent a couple of hours trying to debug this and finally have to admit I'm stuck. I would be grateful for any assistance. Thanks. Bob

Sub CheckForBlanks(Optional HasABlank)
'   The goal of this subroutine is to check for blank cells prior to printing or when the spreadsheet changes.
'   If there are blanks, then they need to be flagged in a different color.
'   Locked cells are ignored.

    Dim MyCell, MyLine As Range
    
    Call ProtectIt
    
    HasABlank = False
    '   Start it with everything OK each check. HasABlank is a Public boolean variable.
        
    For Each MyCell In Range("A1:H58")
    
            If MyCell.Locked = False And MyCell.Value = Empty Then
                If MyCell.MergeCells Then
                    If MyCell.Address <> Left(MyCell.MergeArea.Address, Len(MyCell.Address)) Then
                        GoTo MoveOn
                    End If
                End If
                ' If this cell is in a merged range and isn't the first cell, keep going.
                
                If (MyCell.Address = "$A$8" Or MyCell.Address = "$A$9" Or MyCell.Address = "$A$10" Or _
                    MyCell.Address = "$A$11" Or MyCell.Address = "$A$12") And MyCell.Value = Empty Then GoTo MoveOn
                ' If this cell is in the funding area, but isn't the first line, and if the value is empty, keep going.
                
                If ((InRange((MyCell), Range("B8:H12")) = True) And (Range("A" & MyCell.Row) = "")) Then GoTo MoveOn
                ' I AM GETTING A "RUNTIME ERROR 424 OBJECT REQUIRED" ERROR ON THE ABOVE LINE.
                ' If this cell is in the funding area, but isn't the first line, and if the value of the first
                ' column is empty, keep going.
                
                'If InRange(MyCell, Range("B8:H12")) = True And (Range("A" & MyCell.Row) = "")) Then GoTo TurnLineBlue
                
                ' This point should only be reached if an unlocked cell, first in (or not in) merged range,
                ' is empty, and also if it isn't the first row/column of funding.
                Call UnProtectIt
                MyCell.Interior.ColorIndex = 27
                ' Give the empty, unlocked cell a yellow background.
                Call ProtectIt
                HasABlank = True
                ' This lets the printing routine know that we have at least one blank cell that should have data.
                
            End If
            GoTo MoveOn
            
TurnLineBlue:
        Call UnProtectIt
        For Each MyLine In Range("A" & MyCell.Row & ":H" & MyCellRow)
            MyLine.Interior.ColorIndex = 34
            'MyLine.Value = ""
        Next MyLine
        ' Give the empty, unlocked cell a light blue background.
        Call ProtectIt

MoveOn:
    Next MyCell

End Sub

Function InRange(Range1 As Range, Range2 As Range) As Boolean
' returns True if Range1 is within Range2'
Dim InterSectRange As Range
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
End Function

 

[RoryA]

Where do you get the error?

 

[IndyBob]

Where do you get the error?

I have a comment line in ALL CAPS showing where the error is. It is during the call to the InRange function.

 

[Colin Legg]

In this bit of code here:

InRange((MyCell),

Without going too far into the murky depths of VBA, the pair of brackets around the MyCell is causing it to be de-referenced. Essentially putting a pair of brackets around the object causes it's default method to be called. Take away those red brackets and it should be correctly passed into InRange() as a Range object.

 

[IndyBob]

I added brackets to try to solve a Compiler Error: ByRef Argument Type Mismatch error I was getting. When I remove the brackets, that error returns. I don't understand it, though, because I am passing a Range and the function is expecting a Range. How do I solve that problem? Thanks.

 

[Colin Legg]

You're getting a type mismatch error because MyCell is declared as a Variant, not as a Range.

If you look at your declarations here:

Dim MyCell, MyLine As Range

In VBA, this declares MyCell as a Variant and MyLine as a Range. To declare them both as Range types use:

Dim MyCell As Range, MyLine As Range

 

[IndyBob]

I often wonder if I have to forget something every day to learn something every day.

Thanks for your help. By the way, cool avatar.