CODEWITHSUNDEEP
vbaexcelruntime-error
James G
James G

Reputation: 23

Loop works once, then "Run-time error '424': Object Required"

Very new to coding, and VBA is my first foray into the subject. Took on a project at work and thought "hey, maybe I could macro my way out of this".

Need to go through a column and if StatCell doesn't equal DateCell, I want it to delete StatCell and the three cells to the right. Repeat in that row until we get StatCell=DateCell, then move down to the next row and repeat.

Here's the code I've got so far:

Dim StatRange As Range
Dim StatCell As Range
Dim DateCell As Range
Set DateCell = Range("B1")
Set StatRange = Range("B4:B500")
    For Each StatCell In StatRange
    Do While StatCell.Value <> DateCell.Value
        Range(StatCell.Offset(0, 0), StatCell.Offset(0, 3)).Select
        Selection.Delete Shift:=xlToLeft
    Loop
Next StatCell

It works at first, and does the first deletion as expected, but only once. I then get error 424 in this line: Do While StatCell.Value <> DateCell.Value

Is the fact that I just deleted StatCell and shifted the row over what's causing this error? How do I get around that?

I have a feeling that my error is basic and obvious, but as I said, I'm very new to coding and probably missed a lot of basic lessons. Any help is appreciated!

Upvotes: 2

Views: 859

Answers (3)

Dy.Lee
Dy.Lee

Reputation: 7567

nutsch's code edited.

Sub test()
    Dim StatRange As Range, rgDelete As Range
    Dim StatCell As Range
    Dim DateCell As Range
    Set DateCell = Range("B1")
    Set StatRange = Range("B4:B500")

    For Each StatCell In StatRange.Cells
        If StatCell.Value = DateCell Then Exit For
        'If StatCell.Value <> DateCell.Value Then
            If rgDelete Is Nothing Then
                Set rgDelete = StatCell.Resize(, 4)
            Else
                Set rgDelete = Union(StatCell.Resize(, 4), rgDelete)
            End If
        'End If

    Next StatCell


    If Not rgDelete Is Nothing Then rgDelete.Delete xlToLeft
End Sub

Upvotes: 0

user4039065
user4039065

Reputation:

You are using StatCell looping through StatRange. This is setting StatCell to each cell (aka object or range) within StatRange in turn. If you delete StatCell it becomes Nothing until the range loops and StatCell becomes the next cell in StatRange.

Use row and column numbers to identify the cells instead. These won't become unreferenced and you can continue to loop.

Typically, you would work from bottom to top when deleting rows but you are not deleting entire rows (column A remains untouched despite possible multiple deletions in B:E) so the direction is unimportant in this case.

with worksheets("sheet1")
    dim i as long

    for i=4 to .cells(.rows.count, "B").end(xlup).row
        do while .cells(i, "B").value2 <> .cells(1, "B").value2 and not isempty(.cells(i, "B"))
            .cells(i, "B").resize(1, 4).delete shift:=xltoleft
        loop
    next i

end with

Upvotes: 1

nutsch
nutsch

Reputation: 5962

If you still want to loop through the cells, add all matching ranges to a variable, then delete the ranges you've stored. This will generally process faster than doing deletes one at a time. 

Dim StatRange As Range, rgDelete As Range
Dim StatCell As Range
Dim DateCell As Range
Set DateCell = Range("B1")
Set StatRange = Range("B4:B500")

For Each StatCell In StatRange.Cells

    If StatCell.Value <> DateCell.Value Then
        If rgDelete Is Nothing Then
            Set rgDelete = StatCell
        Else
            Set rgDelete = Union(StatCell.Resize(, 4), rgDelete)
        End If
    End If

Next StatCell


If Not rgDelete Is Nothing Then rgDelete.Delete xlToLeft

Upvotes: 1

Related Questions