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
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:
Set it to Nothing.If Not range_i Is Nothing Then statementHere are some key learnings from these points;
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).
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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments