Here's a function that returns page number depending on pagebreaks ans page
steup order
Cheers,
--
AP
:
'------------------------------------------------------------------
Function pageNo() As String
Dim rCurr As Range
Dim lHoriz As Long
Dim lVert As Long
Dim lPage As Long
Application.Volatile True
Set rCurr = Application.Caller ' Caller's address
With ActiveSheet
'Evaluate Horizontal page breaks
lHoriz = 1
Do While lHoriz <= .HPageBreaks.Count
If rCurr.Row = .HPageBreaks(lHoriz).Location.Row Then
lHoriz = lHoriz + 1
Else
Exit Do
End If
Loop
'Evaluate Vertical page breaks
lVert = 1
Do While lVert <= .VPageBreaks.Count
If rCurr.Column = .VPageBreaks(lVert).Location.Column Then
lVert = lVert + 1
Else
Exit Do
End If
Loop
' Evaluate page # depending on page order
If .PageSetup.Order = xlDownThenOver Then
lPage = lHoriz + (.HPageBreaks.Count + 1) * (lVert - 1)
Else
lPage = lVert + (.VPageBreaks.Count + 1) * (lHoriz - 1)
End If
' Return result
pageNo = lPage & " of " & (.HPageBreaks.Count + 1) *
(.VPageBreaks.Count + 1)
End With
End Function
'----------------------------------------------------------------------------
"pjerling" a écrit dans le message de
news:
...
Hi,
This is a rare occasion where i want to add page numbers to a specific
cell.
i do NOT want to make use of footers/headers etc. how can i create a
function
or any way to have a cell with Page x of y etc.
please help....