How can I get a first cell address of the page-CodePudding

I want to use the "CELL" formula on excel to get address of the first cell of the page.

for example: when I input this formula

=CELL("address";Page1)

it will have output like this

A1

and when I input this formula

=CELL("address";Page2)

it will have output like this

A21

CodePudding user response：

Return Worksheet's First Cell Address (UDF)

VBA

Copy the following code into a standard module e.g. Module1

Option Explicit

Function FirstSheetCell(ByVal WorksheetName As String) As String
    Application.Volatile
    
    Dim ws As Worksheet: Set ws = Application.ThisCell.Worksheet.Parent _
        .Worksheets(WorksheetName)
    With ws.UsedRange
        Dim lCell As Range: Set lCell = .Cells(.Rows.Count, .Columns.Count)
        Dim fCell As Range: Set fCell = .Find("*", lCell, xlFormulas, , xlByRows)
        If Not fCell Is Nothing Then FirstSheetCell = ws.Cells(fCell.Row, _
            .Find("*", lCell, xlFormulas, , xlByColumns).Column).Address(0, 0)
    End With
 
End Function

Excel

In Excel, you can e.g. use the following formula:
```
=FirstSheetCell("Sheet1")
```

CodePudding user response：

Solved, I've created my own code that solve my question

Function FirstPageCell(thepage As String) As String
' Get the first cell of the page

    Dim wks As Worksheet
    Dim iPage As Integer
    Dim iHorPgs As Integer
    Dim iHP As Integer
    Dim lRow As Long

    Set wks = ActiveSheet
    iHorPgs = wks.HPageBreaks.Count   1



    iPage = thepage
    
    iHP = ((iPage - 1) Mod iHorPgs)

    If iHP = 0 Then
        lRow = 1
    Else
        lRow = wks.HPageBreaks(iHP).Location.Row
    End If

    
    FirstPageCell = ("A" & lRow)
End Function

Credit: https://excel.tips.net/T005823_Jumping_To_a_Specific_Page.html