Further Array Element question

[S G Booth]

I'm working with 2 arrays:
varr ... which holds a range value representing the 1st
cell in a Collection page that a page number
can be placed.
and
varrPageNr ... which holds all the page numbers in the
sheet, but not the Collection page numbers.

Ubound(varr) holds the 1st Collection page, not LBound.
Page numbers must paste with a blank row between them ..... max 20 page
numbers per Collection page.

With help ... here's the code after both arrays have been
successfully populated (as best I can tell at this early stage):

Dim k As Long, l As Long, m As Long, rng As Range

For j = UBound(varr) To LBound(varr) Step -1
Debug.Print j & ": " & varr(j)
' set rng to first cell to hold value
Set rng = varr(j)
k = -1: l = 0
For m = LBound(varrPageNr) To UBound _
(varrPageNr)
k = k + 2: l = l + 1
rng(k).Value = "Page " & varrPageNr(m)
If l >= 20 Then Exit For
Next

Next j

I'm trying to take the 1st 20 values in varrPageNr
and paste them into UBound(varr), then take the next
20 values into UBound(varr -1) etc. etc.

My adaptation seems to paste the same 1st 20 values into
the 2nd Collection page.

How to move to the 21st value in varrPageNr as the
routine goes into the 2nd For....Next cycle, please?

 

[Tom Ogilvy]

Dim k As Long, L As Long, m As Long, rng As Range
Dim ub as Long, Dim j as Long
l = lbound(varrPageNr)
For j = UBound(varr) To LBound(varr) Step -1
Debug.Print j & ": " & varr(j)
' set rng to first cell to hold value
Set rng = varr(j)
k = -1
ub = application.Min(L + 19, _
Ubound(varrPageNr))
For m = L To ub
k = k + 2
rng(k).Value = "Page " & varrPageNr(m)
Next
L = ub + 1
Next j

 

[S G Booth]

Works fine, thanks. Very neat!

Regards.

Dim k As Long, L As Long, m As Long, rng As Range
Dim ub as Long, Dim j as Long
l = lbound(varrPageNr)
For j = UBound(varr) To LBound(varr) Step -1
Debug.Print j & ": " & varr(j)
' set rng to first cell to hold value
Set rng = varr(j)
k = -1
ub = application.Min(L + 19, _
Ubound(varrPageNr))
For m = L To ub
k = k + 2
rng(k).Value = "Page " & varrPageNr(m)
Next
L = ub + 1
Next j