Excel 2002, WinXP
My question is general in nature.
I'm helping an OP with a rather large project. At this point he is
getting an error that I am not. We have the same Excel build (version),
exactly, and exactly the same file. The problem is with a macro that deals
with PageBreaks. For your info, that macro is shown below.
The objective of this macro is to get the first PageBreak after
tsLastYear.Row (tsLastYear.Row is 109 in this case). We are working via
email and phone to nail something, anything, down. The problem comes and
goes. The error is a "subscript out of range" in the "IF" statement
argument. Like I said above, I am not having this problem. The presence of
hidden lines seems to be a factor. But not always. Also, he has somewhat
roasted his P3 CPU since his CPU fan quit some time ago and he gets error
messages from that.
I know that PageBreaks in VBA has a bug and I have some notes on that.
My question is: What is the latest information on that bug as regards
workarounds?
A point of interest is that the error occurs in the second cycle of the
"For" loop. This tells me that the code is OK. Thanks for your help. Otto

Sub LocateFirstPB()
Dim k As Long
With Sheets(SameShtRng(1).Value)
For k = 1 To 20
If .HPageBreaks(k).Location.Row tsLastYear.Row Then
FirstPBRow = .HPageBreaks(k).Location.Row
Exit For
End If
Next k
End With
End Sub

Hi Otto

See
http://www.rondebruin.nl/hpagebreaks.htm

Read the KB and see the workeround in the macro on my site

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Otto Moehrbach" wrote in message ...
Excel 2002, WinXP
My question is general in nature.
I'm helping an OP with a rather large project. At this point he is getting an error that I am not. We have the same Excel
build (version), exactly, and exactly the same file. The problem is with a macro that deals with PageBreaks. For your info, that
macro is shown below.
The objective of this macro is to get the first PageBreak after tsLastYear.Row (tsLastYear.Row is 109 in this case). We are
working via email and phone to nail something, anything, down. The problem comes and goes. The error is a "subscript out of
range" in the "IF" statement argument. Like I said above, I am not having this problem. The presence of hidden lines seems to be
a factor. But not always. Also, he has somewhat roasted his P3 CPU since his CPU fan quit some time ago and he gets error
messages from that.
I know that PageBreaks in VBA has a bug and I have some notes on that. My question is: What is the latest information on that
bug as regards workarounds?
A point of interest is that the error occurs in the second cycle of the "For" loop. This tells me that the code is OK. Thanks
for your help. Otto

Sub LocateFirstPB()
Dim k As Long
With Sheets(SameShtRng(1).Value)
For k = 1 To 20
If .HPageBreaks(k).Location.Row tsLastYear.Row Then
FirstPBRow = .HPageBreaks(k).Location.Row
Exit For
End If
Next k
End With
End Sub

Thanks Ron. I'll look at that and incorporate it into my notes. Otto
"Ron de Bruin" wrote in message
...
Hi Otto

See
http://www.rondebruin.nl/hpagebreaks.htm

Read the KB and see the workeround in the macro on my site

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
My question is general in nature.
I'm helping an OP with a rather large project. At this point he is
getting an error that I am not. We have the same Excel build (version),
exactly, and exactly the same file. The problem is with a macro that
deals with PageBreaks. For your info, that macro is shown below.
The objective of this macro is to get the first PageBreak after
tsLastYear.Row (tsLastYear.Row is 109 in this case). We are working via
email and phone to nail something, anything, down. The problem comes and
goes. The error is a "subscript out of range" in the "IF" statement
argument. Like I said above, I am not having this problem. The presence
of hidden lines seems to be a factor. But not always. Also, he has
somewhat roasted his P3 CPU since his CPU fan quit some time ago and he
gets error messages from that.
I know that PageBreaks in VBA has a bug and I have some notes on that.
My question is: What is the latest information on that bug as regards
workarounds?
A point of interest is that the error occurs in the second cycle of the
"For" loop. This tells me that the code is OK. Thanks for your help.
Otto

Sub LocateFirstPB()
Dim k As Long
With Sheets(SameShtRng(1).Value)
For k = 1 To 20
If .HPageBreaks(k).Location.Row tsLastYear.Row Then
FirstPBRow = .HPageBreaks(k).Location.Row
Exit For
End If
Next k
End With
End Sub

I didn't see a KB. What KB were you referring to? Otto
"Ron de Bruin" wrote in message
...
Hi Otto

See
http://www.rondebruin.nl/hpagebreaks.htm

Read the KB and see the workeround in the macro on my site

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
My question is general in nature.
I'm helping an OP with a rather large project. At this point he is
getting an error that I am not. We have the same Excel build (version),
exactly, and exactly the same file. The problem is with a macro that
deals with PageBreaks. For your info, that macro is shown below.
The objective of this macro is to get the first PageBreak after
tsLastYear.Row (tsLastYear.Row is 109 in this case). We are working via
email and phone to nail something, anything, down. The problem comes and
goes. The error is a "subscript out of range" in the "IF" statement
argument. Like I said above, I am not having this problem. The presence
of hidden lines seems to be a factor. But not always. Also, he has
somewhat roasted his P3 CPU since his CPU fan quit some time ago and he
gets error messages from that.
I know that PageBreaks in VBA has a bug and I have some notes on that.
My question is: What is the latest information on that bug as regards
workarounds?
A point of interest is that the error occurs in the second cycle of the
"For" loop. This tells me that the code is OK. Thanks for your help.
Otto

Sub LocateFirstPB()
Dim k As Long
With Sheets(SameShtRng(1).Value)
For k = 1 To 20
If .HPageBreaks(k).Location.Row tsLastYear.Row Then
FirstPBRow = .HPageBreaks(k).Location.Row
Exit For
End If
Next k
End With
End Sub

I found it. All I had to do was look. Otto
"Otto Moehrbach" wrote in message
...
I didn't see a KB. What KB were you referring to? Otto
"Ron de Bruin" wrote in message
...
Hi Otto

See
http://www.rondebruin.nl/hpagebreaks.htm

Read the KB and see the workeround in the macro on my site

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
My question is general in nature.
I'm helping an OP with a rather large project. At this point he is
getting an error that I am not. We have the same Excel build (version),
exactly, and exactly the same file. The problem is with a macro that
deals with PageBreaks. For your info, that macro is shown below.
The objective of this macro is to get the first PageBreak after
tsLastYear.Row (tsLastYear.Row is 109 in this case). We are working via
email and phone to nail something, anything, down. The problem comes
and goes. The error is a "subscript out of range" in the "IF" statement
argument. Like I said above, I am not having this problem. The
presence of hidden lines seems to be a factor. But not always. Also,
he has somewhat roasted his P3 CPU since his CPU fan quit some time ago
and he gets error messages from that.
I know that PageBreaks in VBA has a bug and I have some notes on
that. My question is: What is the latest information on that bug as
regards workarounds?
A point of interest is that the error occurs in the second cycle of the
"For" loop. This tells me that the code is OK. Thanks for your help.
Otto

Sub LocateFirstPB()
Dim k As Long
With Sheets(SameShtRng(1).Value)
For k = 1 To 20
If .HPageBreaks(k).Location.Row tsLastYear.Row Then
FirstPBRow = .HPageBreaks(k).Location.Row
Exit For
End If
Next k
End With
End Sub