Locating the Row Number of PageBreak

[FM1]

How can I locate where the horizontal page break is using a VBA code.

In other word, I am trying to find where
ActiviSheet.HPageBreaks(2).Location is.
When you set up the page break the syntax is

ActiviSheet.HPageBreaks(2).Location = Range(Col, Row)
Col is always "A"
I want my macro to recognize the row number.

My many attempts have been unsuccessfull.

Many Thanks

FM

 

[yogia]

Hi FM:

Try ...

HPB_2 = ActiveWindow.SelectedSheets.HPageBreaks.item(2).Location.Row

In the code line, I had let HPB_2 be the row number for Horizontal PageBreak #2

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[FM1]

Thanks yogia,

I tried it. It gives me a message that "Subscript Out Of Range".

I am trying to use it in a loop so when the code reaches a page break on the sheet it will skip the following 2 (or n) rows and go back to doing what it, hopefully, is supposed to.

Thanks again, I'll keep at it.

FM

 

[yogia]

Hi FM:

I suggest you post your code (with a brief background/description) and point out where it is giving you the error -- and then let us take it from there.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[FM1]

Variables speak for themselves.
spacing is along a row of piles
LL is the distance from the origin of the pile row to the row in the other direction.

this code works.

I am trying to add a step where when the code is filling in the cells with pile coordinates it will skip 2 lines when it gets to a page break and resume with the next I .

I should have posted this to begin with!

Thanks very much.



Sub CORDNT()
NoPiles = ActiveSheet.Range("C5").Value
Spacing = ActiveSheet.Range("D5").Value
LL = ActiveSheet.Range("E5").Value
LTT = (NoPiles - 1) * Spacing
J = 1
For I = 0 To NoPiles
Range("G1").Offset(J, 1).Value = (LTT / 2) - (I * Spacing)
Range("G1").Offset(J, 2).Value = LL
J = J + 1
Next I
End Sub