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

IndyBob

New Member
Joined
Sep 15, 2002
Messages
26
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
Code:
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
 
Last edited by a moderator:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Where do you get the error?
 
Upvote 0
In this bit of code here:

Rich (BB code):
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.
 
Upvote 0
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.
 
Upvote 0
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:
Rich (BB code):
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:
Rich (BB code):
Dim MyCell As Range, MyLine As Range
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top