Excel: object variable or with block not set

Question

Anyone know why the below code halts on range_i.Copy with error? This is related to this question but you don't need to review that question to know the answer to this one I don't think! :-) Thanks

object variable or with block not set

Sub resort()
Dim wb As Workbook, ws As Worksheet, myrange As Range
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")
Set range_i = Nothing
counter = 0

'Find last row
TrE = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

'Start loop assuming data starts in row 2 and 13 columns wide as in example
For Tr = 2 To TrE

    If Not myrange Is Nothing Then
        If ws.Cells(Tr, 13) = 0 Then
            Set myrange = Union(myrange, Range(ws.Cells(Tr, 1), ws.Cells(Tr, 13)))
            counter = counter + 1
        End If
    Else
        If ws.Cells(Tr, 13) = 0 Then
            Set myrange = Range(ws.Cells(Tr, 1), ws.Cells(Tr, 13))
            counter = counter + 1
        End If
    End If

    If Not range_i Is Nothing Then
        If ws.Cells(Tr, 13) > 0 Then
            Set range_i = Union(range_i, Range(ws.Cells(Tr, 1), ws.Cells(Tr, 13)))
        End If
    Else
        If ws.Cells(Tr, 13) > 0 Then
            Set range_i = Range(ws.Cells(Tr, 1), ws.Cells(Tr, 13))
        End If
    End If

 Next Tr

'Create summary sheet

Sheets.Add.Name = "summary"
Set Tws = wb.Sheets("summary")

'Copy ranges into new sheet

offset_i = 2 + counter
myrange.Copy
Tws.Range("A2").PasteSpecial
range_i.Copy
Tws.Range(Cells(offset_i, 1), Cells(offset_i, 13)).PasteSpecial

'Now sort the pasted data for range_i

Tws.Range(Cells(offset_i, 1), Cells(TrE - 1, 13)).Sort key1:=Range("A:A"), _
order1:=xlAscending, Header:=xlNo

'Copy the headers as well

ws.Range("A1:M1").Copy
Tws.Range("A1:M1").PasteSpecial
End Sub

Answer 1

Although you may have figured this out already, here's for the sake of having an answer on a QA site...

As noted by Raymond in the comments: "This error can occur if range_i is nothing..." - And that is exactly why your error occurs.

range_i occurs six times in your code - three key points:

1. You Set it to Nothing.
2. The If Not range_i Is Nothing Then statement
3. Your problem line.

---

Here are some key learnings from these points;

1.

You Set your variable to Nothing but you don't declare it!

By having range_i as an undeclared variable, it's implicitly declared as a Variant data type - not a range.

You should include Dim range_ i As Range as part of your variable declarations - this will help avoid unexpected errors or issues like assigning something other than a range to the variable (Such as a value of the range rather than the range itself).

2.

If Not range_i Is Nothing Then is the same as saying If range_i is Something Then which I don't think is your intention?

3.

You are trying to copy some range intended to be set to a Variant variable that, as outlined above; you have never set because you first set it to Nothing, then prior to setting it to something you check if it's Not Nothing (Something) which it isn't.

Due to these issues you inevitably get the Object variable or With block variable not set Error.

---

You can read the documentation for this error here.