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]

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.