Excel: Error when using an object

[Stoffel24]

I am using the following code to try to add a header at the top of every page. (It's a special one - I know about the excel feature ) However, when I try running it, I get an error about subscript out of range on the last line.

Dim PgeBrk As Object

Set PgeBrk = ActiveSheet.HPageBreaks

For Each PgeBrk In Application.ActiveSheet.HPageBreaks
'Do stuff here
Next


Can anyone suggest what syntax to use and why I am getting this error message?
Thnks

 

[JESTAR]

I think you have to tell it what "next" to do. eg.

Next PgeBrk

...or something

 

[VBslammer]

You don't need to use 'Set' before you use the object, since the 'For-Each' loop will assign the objects for you.

Function GetHBreaks()
  Dim PgeBrk As Excel.HPageBreak

  For Each PgeBrk In Application.ActiveSheet.HPageBreaks
    Debug.Print PgeBrk.Location.Address
  Next

End Function

Sample output:

$A$2
$A$53
$A$104
$A$155
$A$206

VBSlammer

Unemployed in Houston, Texas

 

[Stoffel24]

Hi, a reply to my own query. For some bizarre reason, when I tried my code and VBSlammer's code on a new spreadsheet with bogus data, it works fine. However, on my spreadsheet, it would not work until I put in this line:
ActiveWindow.View = xlPageBreakPreview

So my code looked like this:
Sub PageSetup()
Dim PgeBrk As Excel.HPageBreak

ActiveWindow.View = xlPageBreakPreview 'This is the important line
For Each PgeBrk In Application.ActiveSheet.HPageBreaks

PgeBrk.Location.Select
Blah blah...
Next

ActiveWindow.View = xlNormalView
End sub

Thanks for the help from those that replied.