If I had noticed that activecell reference, I would have changed this line:

If HPB.Location.Row ActiveCell.Row Then Exit For
to
If HPB.Location.Row foundcell.Row Then Exit For

Personally, I make enough typing mistakes that I'd want that check included.

David wrote:

Dave Peterson wrote

Untested, but it uses the code you posted:

Option Explicit
Sub test()
Dim VPC As Long
Dim HPC As Long
Dim HPB As HPageBreak
Dim NumPage As Long
Dim myNames As Variant
Dim iCtr As Long
Dim FoundCell As Range

myNames = Array("turner, david", "turner, kathleen")

For iCtr = LBound(myNames) To UBound(myNames)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr))
If FoundCell Is Nothing Then
MsgBox myNames(iCtr) & " wasn't found"
Else
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage,
Preview:=True
End If
Next iCtr
End Sub


(I changed "as integer" to "as long".)

You may want to provide all the parms to your .find statement.
Excel/VBA remembers what was used. So you may be disappointed if the
user (or code) did a .find with matchcase:=true (for example).

Since code after Else calculates for active cell, I simply had to add
FoundCell.Activate after Else

or if I were positive all names in the array were present, I could
eliminate any FoundCell code altogether with

Range("A:A").Find(What:=(myNames(iCtr))).Activate

My tested, working code:
Sub PrintMine()
Dim VPC As Long, HPC As Long, HPB As HPageBreak
Dim iCtr As Long, NumPage As Long, MyNames As Variant
MyNames = Array("turner, david", "turner, kathleen")
For iCtr = LBound(MyNames) To UBound(MyNames)
Range("A:A").Find(What:=(MyNames(iCtr))).Activate
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage, Preview:=True
Next iCtr
End Sub

Thanks for providing needed array handling syntax.

--
David

--

Dave Peterson