Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page# Return off lookup
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page# Return off lookup
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page# Return off lookup
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page# Return off lookup
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page# Return off lookup
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Page# Return off lookup
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup and return value | Excel Worksheet Functions | |||
LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
Lookup and return | Excel Worksheet Functions | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) | |||
Lookup from first row, return last row value | Excel Programming |