Получения номера страницы по выделенной ячейки

[brezze]

необходимо получить номер страницы на которой будет распологаться выделенная ячейка при печати. Возможно ли такое с помощью VB

[Serge_Bliznykov]

на удивление, это оказалась нетривиальная и достаточно сложная задача... :-(
Общая идея в том, чтобы брать позиции горизонтальных и вертикальных линий разбивки листа и вручную отсчитывать страницы. к счастью, через поиск здесь нашёл решение от Brad byundt

Function PageNumber(cel As Range, Optional bEntireWorkbookNumbering As Boolean = False, _
    Optional vUpdatePageNumbering As Variant, Optional bAcrossThenDown As Boolean = False) As Long
Dim ws As Worksheet
Dim rgBreaks As Range
Dim hBreak As HPageBreak
Dim vBreak As VPageBreak
Dim i As Long, j As Long, k As Long, n As Long
Set ws = cel.Parent
For Each hBreak In ws.HPageBreaks
    If hBreak.Location.Row > cel.Row Then Exit For
    i = i + 1
Next
For Each vBreak In ws.VPageBreaks
    If vBreak.Location.Column > cel.Column Then Exit For
    j = j + 1
Next
If bAcrossThenDown Then 'Print pages across, then down
    PageNumber = i * ws.HPageBreaks.Count + j + 1
Else    'Print pages down, then across
    PageNumber = j * ws.VPageBreaks.Count + i + 1
End If
If bEntireWorkbookNumbering Then
    n = ws.Index
    If n > 1 Then
        n = n - 1
        For k = 1 To n
            Select Case Sheets(k).Type
            Case xlChart
                PageNumber = PageNumber + 1
            Case xlWorksheet
                i = Worksheets(k).HPageBreaks.Count
                j = Worksheets(k).VPageBreaks.Count
                PageNumber = PageNumber + (i + 1) * (j + 1)
            End Select
        Next
    End If
End If
End Function

Текст от автора кода:

therefore changed tactics and wrote a VBA user-defined function. You would use this UDF in a worksheet formula like:
=PageNumber('Chapter 2'!A27,FALSE) where A27 is the start of a particular section in the Chapter 2 worksheet. FALSE means to return the page number starting at 1

And if you wanted the page numbering for Chapter 2 to start with the first number after the end of Chapter 1, then you might use a formula like:
=PageNumber('Chapter 2'!A27,TRUE,$I$1) where I1 contains a "trigger". If the trigger is changed, then the page number recalculates. TRUE means to return the page number starting with the last page in the previous Chapter.

To force the function to update, I used cell I1 as a "trigger". This trigger is pulled whenever the value of cell I1 is changed, and all the page numbers recalculate. A macro automates the process of changing cell I1 whenever the first worksheet is activated using this sub:

Private Sub Worksheet_Activate()
[I1].Formula = [I1].Value 'This forces all the page numbers to update
End Sub

Brad

p.s. я не смог добиться нормально работы, когда стоит последовательность вывода страниц "вправо, затем вниз".. :-(
При обычной "вниз, затем вправо" всё вроде нормально!