Page# Return off lookup

[Guest]

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

 

[Guest]

start with

=Match([Car Type],F1:f25000,0)/60

assumes 60 entries per page. Adjust to fit your actual situation.

 

[Guest]

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.

start with

=Match([Car Type],F1:f25000,0)/60

assumes 60 entries per page. Adjust to fit your actual situation.

--
Regards,
Tom Ogilvy

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

 

[Guest]

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

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.

start with

=Match([Car Type],F1:f25000,0)/60

assumes 60 entries per page. Adjust to fit your actual situation.

--
Regards,
Tom Ogilvy

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

 

[Guest]

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

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

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.

start with

=Match([Car Type],F1:f25000,0)/60

assumes 60 entries per page. Adjust to fit your actual situation.

--
Regards,
Tom Ogilvy



:

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

 

[Guest]

This worked, but only if I was in pagebreak preview mode - It might work for
you without being in preview mode, but I wouldn't count on it.:
Also, almost anything associated with pagesetup type information is going to
be slow.

Function CheckPage(s As String, rng As Range)
Dim VPC As Integer, HPC As Integer
Dim NumPage As Integer, i As Long
res = Application.Match(s, rng, 0)
If Not IsError(res) Then
Set rng1 = rng(res)
If rng.Parent.PageSetup.Order = xlDownThenOver Then
HPC = rng.Parent.HPageBreaks.Count + 1
VPC = 1
Else
VPC = rng.Parent.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1
Set v = rng.Parent.VPageBreaks
For i = 1 To v.Count
If v(i).Location.Column > rng1.Column Then Exit For
NumPage = NumPage + HPC
Next i
Set h = rng.Parent.HPageBreaks
For i = 1 To h.Count
If h(i).Location.Row > rng1.Row Then Exit For
NumPage = NumPage + VPC
Next i
CheckPage = NumPage
Else
CheckPage = False
End If
End Function

--
Regards,
Tom Ogilvy

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

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

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.

:

start with

=Match([Car Type],F1:f25000,0)/60

assumes 60 entries per page. Adjust to fit your actual situation.

--
Regards,
Tom Ogilvy



:

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