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

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

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

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

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

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


"FASTWRX" wrote:

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