Run-time error '91': Object variable or With block variable not set after code runs

Question

I'm writing my first VBA code and have a complex set of steps working on data in Excel. Everything works fine except this one sub, which runs fine, but gives an error at the end, which I'm pretty sure is because at some point after the data is exhausted, it returns "nothing" and that causes the "Run-time error '91': Object variable or With block variable not set" crash. I'm stuck with how to fix this. Any suggestions? I'm sure it's something simple - and yes, this code is slow, but it works. Thanks!

Sub D_GSB_Sort_Data()
Dim MyCell As Range
    For Each MyCell In Range("A2:A5000")
        ActiveWorkbook.ActiveSheet.Columns(1).Find("*Submitted*").Select
        ActiveCell.Offset(0, 3).Cut ActiveCell.Offset(0, 0)
        ActiveCell.Offset(1, 1).Cut ActiveCell.Offset(0, 1)
        ActiveCell.Offset(2, 1).Cut ActiveCell.Offset(0, 2)
        ActiveCell.Offset(3, 1).Cut ActiveCell.Offset(0, 3)
        ActiveCell.Offset(4, 0).Cut ActiveCell.Offset(0, 4)       
Next MyCell
End Sub

You need to test if the Find succeeded before attempting to do anything else, including the Select. — BigBen
– BigBen, Commented Jul 1, 2020 at 18:10
It might be a lot easier to use Range.AutoFilter here instead of Find though. — BigBen
– BigBen, Commented Jul 1, 2020 at 18:11
No need for Find() if you're already looping over the range - just use If MyCell.Value Like "*Submitted *" Then and use MyCell in place of ActiveCell — Tim Williams
– Tim Williams, Commented Jul 1, 2020 at 18:16
I changed the code to beSub GSB_Sort_Data() Dim MyCell As Range For Each MyCell In Range("A2:A5000") If MyCell.Value Like "*Submitted*" Then MyCell.Offset(0, 3).Cut MyCell.Offset(0, 0) MyCell.Offset(1, 1).Cut MyCell.Offset(0, 1) MyCell.Offset(2, 1).Cut MyCell.Offset(0, 2) MyCell.Offset(3, 1).Cut MyCell.Offset(0, 3) MyCell.Offset(4, 0).Cut MyCell.Offset(0, 4) Next MyCell End Sub but now get a "Next without For" error — JoeDeVico
– JoeDeVico, Commented Jul 1, 2020 at 19:00
You're missing an End If... add it prior to Next MyCell. — BigBen
– BigBen, Commented Jul 1, 2020 at 19:03

Tim Williams · Accepted Answer · 2020-07-01 22:43:30Z

2

Edit: cutting another cell to MyCell will break that reference, so do that last...

Sub D_GSB_Sort_Data()
    Dim MyCell As Range, ws As Worksheet

    Set ws = ActiveSheet

    For Each MyCell In ws.Range("A2:A" & _
                          ws.cells(ws.Rows.Count, 1).End(xlUp).Row).Cells

        If MyCell.Value Like "*Submitted*" Then
            With MyCell
                .Offset(1, 1).Cut .Offset(0, 1)
                .Offset(2, 1).Cut .Offset(0, 2)
                .Offset(3, 1).Cut .Offset(0, 3)
                .Offset(4, 0).Cut .Offset(0, 4)
                .Offset(0, 3).Cut .Offset(0, 0) '<< this one last!
            End with
        End If
   
    Next MyCell
End Sub

edited Jul 1, 2020 at 22:43

answered Jul 1, 2020 at 19:25

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JoeDeVico Over a year ago

Thanks Tim! but this gives me the same result as mentioned above. "Object Required" error on the 2nd offset line Offset(1, 1).Cut .Offset(0, 1)

Tim Williams Over a year ago

Ok I just noticed you’re essentially replacing MyCell with the first cut. - I can fix later or just move that first cut to the last operation

JoeDeVico Over a year ago

That did it - Thanks so much - That was my suspicion. Much Appreciated!!!

Collectives™ on Stack Overflow

Run-time error '91': Object variable or With block variable not set after code runs

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related