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:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,312
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Where do you get the error?
 

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
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.
 

IndyBob

New Member
Joined
Sep 15, 2002
Messages
26
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

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
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
 

IndyBob

New Member
Joined
Sep 15, 2002
Messages
26
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.
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top