Making a multipage report with Excel Macro

[earthsci]

Hello,
I need to make a report using an Excel Macro that will write to a new page when the previous page is filled. I set up a For Loop to go through records in a dbf which will determine the number of pages to write out to. This is what I have written to test whether or not to go to the next page
It only works for the first few pages

'Test to see if at bottom of page

If ((53 * (NumPages + 1)) > (NumStart + 39))Then
NumStart = NumStart + 19
Else
NumPages = NumPages + 1
NumStart = (58 * NumPages) + 1
End If

 

[RobBroekhuis]

What happens after the first few pages (actual values of variables would help)? Are you sure that you mean to have 53 in the first line and 58 a little further along?
It looks like you write your data in constant-length chunks of 19 lines. If this is really the case, then you know exactly how many chunks will fit on each page (3, most likely), and you don't need this test - just do a check for

ChunkNr=ChunkNr+1
if ChunkNr mod 3=0 then
numpages=numpages+1
end if

Or something similar.
Rob

 

[earthsci]

Yes, I am writing two chunks of data consisting of 19 lines each to each page. Each page is 57 lines long. I had not tried it using the modulus, but I did get it to work finally. Your solution is much cleaner than what I came up with (as you can see) so I will probably switch to a check using the modulus
Thanks for your reply

NumPages = 0
NumStart = NumPages + 1

If ((57 * (NumPages + 1)) - 5 > (NumStart + 35)) Then
NumStart = NumStart + 19
Else
NumPages = NumPages + 1
NumStart = (57 * NumPages) + 1
End If