Phone Ring ……… Phone Ring ……… The CID informed me that it was my good friend Timos on the other end .
Timos : Hey Nickos , Good morning can you tell me if there is an easy way to get the page number of a cell ??? I want to summarize a column based on page breaks
Me: Hmmmmm . Interesting problem . Let me get back to you in a while.
I must admit . that was a thing i could easily name , an interesting problem . Until that time the page number in excel for me was something that existed in the sphere of print settings and i had never involved it in and calculation inside the sheet .
That was surely an interesting problem !!!
Did some search and found some interesting stuff in forum posts .
The best one i found was mentioned in ( http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/8ca260935d83f46a )
Here is the solution transformed in VBA Function you can easily insert into Excel VBA Editor .
Public Function getpagenumber(CurrentCell As Range) As String
Dim VPC As Integer, HPC As Integer
Dim VerticalPageBreak As VPageBreak, HorizontalPageBreak As HPageBreak
Dim NumPage As Integer
If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1
For Each VerticalPageBreak In ActiveSheet.VPageBreaks
If VerticalPageBreak.Location.Column > CurrentCell.Cells.Column Then Exit For
NumPage = NumPage + HPC
Next VerticalPageBreak
For Each HorizontalPageBreak In ActiveSheet.HPageBreaks
If HorizontalPageBreak.Location.Row > CurrentCell.Cells.Row Then Exit For
NumPage = NumPage + VPC
Next HorizontalPageBreak
getpagenumber = NumPage
End Function