Long shot - bookmap

[kayos]

Hey folks

this is a bit of a long shot but maybe someone can guide me on th
right path.
If anyone is familiar with the textbook publishing business, you ma
have heard of what is called bookmaps. It is literally a layout fil
that contains a series of rectangles that represent the pages involve
with the book so when you are putting together a 250 page textbook yo
have an idea of what content will go where and how much space you wil
need.
Everyone I know that uses bookmaps creates them themselves in Excel fo
the simple reason that it is easiest to use the cells to make "pages
and comment fields below them. The problem is that the page numbers ca
rise or drop depending on the content of the pages so the page count ca
flex many times throughout the project. Then the cells of the book page
have to be repaginated by hand each time the page numbers change whic
can be a painstaking process when hundreds of pages are involved.
I have included an example of the bookmap with this posting.
Is there a way to make the cells automatically renumber themselves whe
a cell is removed. For instance - if I have cells numbered 1 throug
ten, then remove cell 8, can the cells be set up to automatically tur
cell "9" into "8", "10" into "9", etc.
I realize this may be a bit confusing and perhaps isn't th
apoplication to do this with but there is literally NO other existin
software out there that will do this and I don't have the codin
experience to write anything.

Thanks for any help
Keit

+-------------------------------------------------------------------
|Filename: bookmap.xls.zip
|Download: http://www.excelforum.com/attachment.php?postid=3449
+-------------------------------------------------------------------

 

[Bernie Deitrick]

Keith,

The link to your attachment came back with:
"Invalid Attachment specified. If you followed a valid link, please notify
the webmaster"


HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

This might be a start.

Sub NumberPages()
k = -1
For i = 4 To 208 Step 4
For j = 1 To 14
Set rng = Cells(i, j)
If Not IsEmpty(rng.Offset(0, -2)) Then
k = k + 1
If k <> 0 Then
Set rng = Cells(i, j)
If IsNumeric(rng) Then
rng = k
Else
For m = 1 To Len(rng)
sChr = Mid(rng, m, 1)
If Not IsNumeric(sChr) Then
sChr1 = sChr1 & sChr
End If
Next
rng = sChr1 & k
End If
End If
End If
Next j
Next i
End Sub

 

[Tom Ogilvy]

You have to log into the Excelforum and use it from there.

--
Regards,
Tom Ogilvy

Keith,

The link to your attachment came back with:
"Invalid Attachment specified. If you followed a valid link, please notify
the webmaster"


HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,
Click on the "View this thread" link and you should get to the
download ZIP file.

Keith,

The link to your attachment came back with:
"Invalid Attachment specified. If you followed a valid link, please notify
the webmaster"


HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Tom,

Won't there be a problem when j = 1 and 2?

Set rng = Cells(i, j)
If Not IsEmpty(rng.Offset(0, -2)) Then

HTH,
Bernie
MS Excel MVP

This might be a start.

Sub NumberPages()
k = -1
For i = 4 To 208 Step 4
For j = 1 To 14
Set rng = Cells(i, j)
If Not IsEmpty(rng.Offset(0, -2)) Then
k = k + 1
If k <> 0 Then
Set rng = Cells(i, j)
If IsNumeric(rng) Then
rng = k
Else
For m = 1 To Len(rng)
sChr = Mid(rng, m, 1)
If Not IsNumeric(sChr) Then
sChr1 = sChr1 & sChr
End If
Next
rng = sChr1 & k
End If
End If
End If
Next j
Next i
End Sub

 

[Bernie Deitrick]

Keith,

Given that your "page" covers three rows in a column, what do you do to
'delete' a page?


For example, the page "Welcome to School" is in cells J26, J27, and J28. Do
you delete the contents of all of those, or of just one? And what should
happen to the other cells associated with that page? A good before and
after example covering, say, these two pages:

Welcome to School <Blank>
W-2 W-3
66 67


where you want to delete page 66 - would be helpful. What would you delete,
and what should move to fill in?

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

Bernie, thanks.
my typo

If Not IsEmpty(rng.Offset(0, -2)) Then

should have been

If Not IsEmpty(rng.Offset(-2, 0)) Then

 

[Guest]

Use one cell as your starting point then add 1 to subsequent cells i.e. put 1
into A1, A1+1 into A2, A2+1 into A3 etc.