Wow. Sorry to be a pain, but I'm getting a error window that says "That name
is not valid". I'm using =CheckPage() in the active worksheet
"Tom Ogilvy" wrote:
Sub CheckPage()
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak, HPB As HPageBreak
Dim NumPage As Integer
Dim rng as Range
rng = Range("F1:F25000")
res = Application.match(s,rng,0)
if not iserror(res) then
cells(res,"F").Activate
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 VPB In ActiveSheet.VPageBreaks
If VPB.Location.Column ActiveCell.Column Then Exit For
NumPage = NumPage + HPC
Next VPB
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
MsgBox "Page number of the active cell = " & NumPage
' or
'Msgbox = "Page " & NumPage & " of " & _
' Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
end if
End sub
adapted from code previously posted by Laurent Longre.
--
Regards,
Tom Ogilvy
"FASTWRX" wrote:
Tom . . . thank you, but I don't think this would work as the RowHeight and
entries per page are not consistent. Any other ideas? I was thinking it
would need to be a VB equation - but I'm not as knowledgeable in this area.
"Tom Ogilvy" wrote:
start with
=Match([Car Type],F1:f25000,0)/60
assumes 60 entries per page. Adjust to fit your actual situation.
--
Regards,
Tom Ogilvy
"FASTWRX" wrote:
I'm building a table of contents for a parts catalog and would like to the
page number returned off a lookup through a specific column. As I would see
it, the lookup would be somethink like this: (car type would be in TOC)
vlookup([Car Type],F2:f25000,PageNumber())
I have a problem with getting the PageNumber the item falls on to be
returned. Any ideas? TIA