Excel VBA: Object not set in second loop

Question

In the second iteration of a loop, VBA crashes setting TestRange, which is dependent on another range CurrentDataTop, saying "object required...".

I set CurrentDataTop at the start of each loop, and the first iteration runs through OK.

Option Explicit

Sub AMystery()

Dim OutBook As Workbook, MasterWorkbook As Workbook
Dim CurrentDataTop As Range, CurrentData As Range, TestRange As Range
Dim Index As Double, i As Double, iTop As Double, iBottom As Double, iColumn As Double

Set MasterWorkbook = ActiveWorkbook

'MainLoopStart
For Index = 1 To 3

    MasterWorkbook.Sheets.Copy
    ActiveWorkbook.SaveAs Filename:=MasterWorkbook.Path & "\NewName_" & Index & ".xlsx", FileFormat:=51
    Set OutBook = Workbooks("NewName_" & Index & ".xlsx")
    OutBook.Activate

    ' Set new range starts, within the duplicate book
    Set CurrentDataTop = OutBook.Worksheets("Sheet3").Range("E6")

        'Delete some data not required
            CurrentDataTop.Worksheet.Activate
            iTop = CurrentDataTop.Row
            iColumn = CurrentDataTop.Column
            iBottom = Cells(Rows.Count, CurrentDataTop.Column).End(xlUp).Row
            For i = iBottom To iTop Step -1
                If Cells(i, iColumn).Value <> Index Then
                    Rows(i).Delete
                End If
            Next i

        'IT CRASHES HERE ON THE SECOND LOOP, SAYING "OBJECT REQUIRED"
        Set TestRange = CurrentDataTop.Offset(0, 10)
        TestRange.Value = "Some text"

        'Save & close new book
        OutBook.Save
        OutBook.Close

'MainLoopEnd
Next Index

End Sub

YowE3K · Accepted Answer · 2016-11-24 23:56:22Z

1

Cell E6 in the original workbook probably contained a value of 1.

Therefore, on the second iteration, your code will delete the row containing CurrentDataTop (i.e. cell E6) because the cell does not contain a 2.

When you go to Offset from a non-existent cell, the code will crash as Offset can only be performed if there is an object to offset from.

answered Nov 24, 2016 at 23:56

YowE3K

24k7 gold badges28 silver badges45 bronze badges

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

1 Comment

Chuckles Over a year ago

Thanks! Looks like I was being a muppet there, but you saved the day.

user6432984 · Accepted Answer · 2016-11-25 01:20:35Z

0

The problem is that you are deleted all of CurrentDataTop's cells.

answered Nov 25, 2016 at 1:20

user6432984

2 Comments

Chuckles Over a year ago

Thanks also for this recording - very useful :)

user6432984 Over a year ago

Thanks, I/m glad that you liked it. I started trying to show debugging techniques.

Collectives™ on Stack Overflow

Excel VBA: Object not set in second loop

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related