Select method of Range class failure

[Stuart]

With ws

loads of code
then

'Locate the end of each page and insert the Collection formulae
For j = 1 To UBound(Pagevarr) - 1
StartToCollectionRow = Pagevarr(j).Offset(1, 6).Row
EndToCollectionRow = Pagevarr(j + 1).Offset(-3, 6).Row
Pagevarr(j + 1).Offset(-1, 7).Formula = "=sum(H" & _
StartToCollectionRow & ":H" & EndToCollectionRow & ")"
Next

which works fine and them immediately after:

'Transfer the page totals to the Collection page
Pagevarr(j).Offset(1, 1).End(xlDown).Select
If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _
ActiveCell.Value = "COLLECTION (Cont.)" Or _
ActiveCell.Value = "GENERAL SUMMARY") Then
MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _
ActiveCell.Address
GoTo Line5
Else
etc

The code fails on Pagevarr(j).Offset(1, 1).End(xlDown).Select

Why is this, please?

Regards.

 

[Tom Ogilvy]

Check the value of j at that point

msgbox j, ubound(pagevarr)
msgbox Pagevarr(j).Address
msgbox Pagevarr(j).Offset(1,1).End(xldown).Address
Pagevarr(j).Offset(1, 1).End(xlDown).Select
If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _
ActiveCell.Value = "COLLECTION (Cont.)" Or _
ActiveCell.Value = "GENERAL SUMMARY") Then
MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _
ActiveCell.Address
GoTo Line5
Else

I am not sure I would depend on the value of j being what you would expect
it to be or, if it is what you expect it to be, is Pagevarr(j) set to a
range. If it is, is the range on the last row or column?

 

[Stuart]

Many thanks.
In this case:
msgbox j, ubound(pagevarr) returns '4' which is correct
msgbox Pagevarr(j).Address returns A145, also correct
msgbox Pagevarr(j).Offset(1,1).End(xldown).Address returns
B148, also correct.
ws is referencing the correct worksheet.
Pagevarr(4) is shown as Variant/Object/Range in Locals
In this case, I have data down in H200.

Regards.

 

[Stuart]

Don't know if this will shed any light on my errors, but this
alteration appears to work:

'TEST CODE omitting the Select statement
If Not (Pagevarr(j).Offset(1, 1).End(xlDown).Value = "COLLECTION" Or _
Pagevarr(j).Offset(1, 1).End(xlDown).Value = _
"COLLECTION (Cont.)" Or Pagevarr(j).Offset(1, 1).End(xlDown) _
Value = "GENERAL SUMMARY") Then
MsgBox "There is a missing COLLECTION in: " & ws.Name & "..." & _
Pagevarr(j).Offset(1, 1).End(xlDown).Address
GoTo Line5
Else
Set Target = Pagevarr(j).Offset(1, 1).End(xlDown).End(xlDown) _
.Offset(0, 6)
For j = 1 To UBound(Pagevarr) - 1
Target.Value = Pagevarr(j + 1).Offset(-1, 7).Value
Set Target = Target.Offset(2, 0)
Next
End If

'Set the formula for the COLLECTION page
StartToCollectionRow = Pagevarr(j).Offset(1, 6).Row
EndToCollectionRow = .Range("F65536").End(xlUp).Offset(-2, 0).Row
..Range("H" & EndToCollectionRow + 2).Value = "=sum(H" _
& StartToCollectionRow & ":H" & EndToCollectionRow & ")"

Regards.