Page Numbering Macro Code

[austinandrei]

Hi,
I have a set of data in Column A which varies from 1 to 1000, and there maybe more than 1000 line items.
Sample is below which only consist of 5 items, Row1 is always a header and is not counted:
[TABLE="width: 196"]
<tbody>[TR]
[TD]ITEM[/TD]
[TD]Entity[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]500249[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]500249[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]500249[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]500249[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]500249[/TD]
[/TR]
</tbody>[/TABLE]
What I need is to identify the pages in Column C, each page consist of 50items.
In example above, all are identified as "Page1" as it is only about 5items.
If it hits 60 items, the first 50 will be identified as "Page1" and the items from 51-60 will be identified as "Page2".
But if all the list items exceeds 1000, then all will be in error, no more paging.
1000 will be the limit of page identification.
Here's the simple code that will work for less criteria:

Sub PDFPage()


itemcount = Range("B" & Rows.Count).End(xlUp).Offset().row
pagenumber = 1


For paging = 2 To itemcount


If Range("A" & paging) > 50 Then
    Range("C" & paging).Value = "Page" & pagenumber
'Next ElseIf if it is 51-100 and so on and so on


End If


Next


End Sub

austin

 

[dermie_72]

you could try this

Sub PDFPage()
Dim count As Long
itemcount = Range("B" & Rows.count).End(xlUp).Offset().Row
pagenumber = 1
count = 1
For paging = 2 To itemcount
    If count > 50 Then
        count = 1
        pagenumber = pagenumber + 1
        Else
    End If
    Range("C" & paging).Value = "Page " & pagenumber
    count = count + 1
Next
End Sub

 

[austinandrei]

Hi dermie
This great.
One last requirement, that if it hits 1000, if will no longer be Page1, Page2 and so on.it will automatically writes "ERROR", too many data.
Thanks!

 

[dermie_72]

just above If count > 50 Then
put

if pagenumber = 3 then
    pagenumber = "ERROR"
    else
end if

 

[austinandrei]

Hi Dermie,
The added code will just make the Page3 as pageError but all of it should be in error once the items are counted as 1000.
But I already found a way wherein before your codes, if itemcount is >1000, it won't run the codes but instead put "ERROR"

Sub PDFPage()
Dim count As Long
itemcount = Range("B" & Rows.count).End(xlUp).Offset().Row


pagenumber = 1
count = 1
For paging = 2 To itemcount
If itemcount > 1001 Then
    Range("C" & paging).Value = "ERROR"
Else
    If count > 50 Then
        count = 1
        pagenumber = pagenumber + 1
        Else
    End If
    Range("C" & paging).Value = "Page " & pagenumber
    count = count + 1
End If
Next


End Sub

Thanks a lot for your help!