Use Named Range instead?

With a lot of help from folks here, I've got the following working:
Sub PrintMine()
Dim HPB As HPageBreak, FoundCell As Range
Dim iCtr As Long, NumPage As Long, myNames As Variant
myNames = Array( _
"Name1, Name1", "Name2, Name2", "Name3, Name3")
For iCtr = LBound(myNames) To UBound(myNames)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr))
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row FoundCell.Row Then Exit For
NumPage = NumPage + 1
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage, preview:=True 'for testing
Next iCtr
End Sub

Where Names are actually Lastname, Firstname of desired list from total
names on the sheet.

Every time that list changes, I have to edit the code and make sure I spell
the new name(s) right and presumably make sure they're in alphabetical
order (my assumption).

Thought occured to me I could select them from a list, give that list a
named range and use that, but I can't figure out how. Any efforts have
resulted in Type Mismatch errors. i.e. myNames = Range("MyList").Value or
..Value2 or .Text

Any help?

--
David

Which line is giving the error?

It shouldn't be this line with the code you posted:

myNames = Array("Name1, Name1", "Name2, Name2", "Name3, Name3")

But picking up an array from a worksheet will result in a two dimensional
array--even if that second dimension is one (rows by columns, x rows by 1 column
in your case).

Maybe this change would make it work.

myNames = Worksheets("placenamehere").Range("myList").Value
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr, 1))



David wrote:

With a lot of help from folks here, I've got the following working:
Sub PrintMine()
Dim HPB As HPageBreak, FoundCell As Range
Dim iCtr As Long, NumPage As Long, myNames As Variant
myNames = Array( _
"Name1, Name1", "Name2, Name2", "Name3, Name3")
For iCtr = LBound(myNames) To UBound(myNames)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr))
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row FoundCell.Row Then Exit For
NumPage = NumPage + 1
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage, preview:=True 'for testing
Next iCtr
End Sub

Where Names are actually Lastname, Firstname of desired list from total
names on the sheet.

Every time that list changes, I have to edit the code and make sure I spell
the new name(s) right and presumably make sure they're in alphabetical
order (my assumption).

Thought occured to me I could select them from a list, give that list a
named range and use that, but I can't figure out how. Any efforts have
resulted in Type Mismatch errors. i.e. myNames = Range("MyList").Value or
.Value2 or .Text

Any help?

--
David

--

Dave Peterson

Dave Peterson wrote

Which line is giving the error?

It shouldn't be this line with the code you posted:

myNames = Array("Name1, Name1", "Name2, Name2", "Name3, Name3")

It bombs he
For iCtr = LBound(myNames) To UBound(myNames)


But picking up an array from a worksheet will result in a two
dimensional array--even if that second dimension is one (rows by
columns, x rows by 1 column in your case).

Maybe this change would make it work.

myNames = Worksheets("placenamehere").Range("myList").Value
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr, 1))

Same error, same line.

I did find this syntax after further searching, and it works!!:

Dim HPB As HPageBreak, FoundCell As Range
Dim c As Variant, NumPage As Long
For Each c In Range("myList")
Set FoundCell = Range("A:A").Find(What:=c)

Thanks for trying, anyway.

--
David


David wrote:

With a lot of help from folks here, I've got the following working:
Sub PrintMine()
Dim HPB As HPageBreak, FoundCell As Range
Dim iCtr As Long, NumPage As Long, myNames As Variant
myNames = Array( _
"Name1, Name1", "Name2, Name2", "Name3, Name3")
For iCtr = LBound(myNames) To UBound(myNames)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr))
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row FoundCell.Row Then Exit For
NumPage = NumPage + 1
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage, preview:=True 'for
testing Next iCtr
End Sub

Where Names are actually Lastname, Firstname of desired list from
total names on the sheet.

Every time that list changes, I have to edit the code and make sure I
spell the new name(s) right and presumably make sure they're in
alphabetical order (my assumption).

Thought occured to me I could select them from a list, give that list
a named range and use that, but I can't figure out how. Any efforts
have resulted in Type Mismatch errors. i.e. myNames =
Range("MyList").Value or .Value2 or .Text

Any help?

--
David

Glad you got it working, but there was a difference in those lines.

"Same error, same line" was "no error, different line" for me.

David wrote:

Dave Peterson wrote

Which line is giving the error?

It shouldn't be this line with the code you posted:

myNames = Array("Name1, Name1", "Name2, Name2", "Name3, Name3")

It bombs he
For iCtr = LBound(myNames) To UBound(myNames)

But picking up an array from a worksheet will result in a two
dimensional array--even if that second dimension is one (rows by
columns, x rows by 1 column in your case).

Maybe this change would make it work.

myNames = Worksheets("placenamehere").Range("myList").Value
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr, 1))

Same error, same line.

I did find this syntax after further searching, and it works!!:

Dim HPB As HPageBreak, FoundCell As Range
Dim c As Variant, NumPage As Long
For Each c In Range("myList")
Set FoundCell = Range("A:A").Find(What:=c)

Thanks for trying, anyway.

--
David

David wrote:

With a lot of help from folks here, I've got the following working:
Sub PrintMine()
Dim HPB As HPageBreak, FoundCell As Range
Dim iCtr As Long, NumPage As Long, myNames As Variant
myNames = Array( _
"Name1, Name1", "Name2, Name2", "Name3, Name3")
For iCtr = LBound(myNames) To UBound(myNames)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr))
NumPage = 1
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row FoundCell.Row Then Exit For
NumPage = NumPage + 1
Next HPB
Sheets(1).PrintOut From:=NumPage, To:=NumPage, preview:=True 'for
testing Next iCtr
End Sub

Where Names are actually Lastname, Firstname of desired list from
total names on the sheet.

Every time that list changes, I have to edit the code and make sure I
spell the new name(s) right and presumably make sure they're in
alphabetical order (my assumption).

Thought occured to me I could select them from a list, give that list
a named range and use that, but I can't figure out how. Any efforts
have resulted in Type Mismatch errors. i.e. myNames =
Range("MyList").Value or .Value2 or .Text

Any help?

--
David


--

Dave Peterson

Dave Peterson wrote

Glad you got it working, but there was a difference in those lines.

"Same error, same line" was "no error, different line" for me.


Maybe just a lack of clarity on my part. Substituted your code for my
original and got Type Mismatch error at new line:
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
instead of at my original line:
For iCtr = LBound(myNames) To UBound(myNames)

Not sure if this is pertinent, but each name in "A:A" is separated by
several rows.

Just trying to understand.

--
David

It still worked ok for me.

David wrote:

Dave Peterson wrote

Glad you got it working, but there was a difference in those lines.

"Same error, same line" was "no error, different line" for me.


Maybe just a lack of clarity on my part. Substituted your code for my
original and got Type Mismatch error at new line:
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
instead of at my original line:
For iCtr = LBound(myNames) To UBound(myNames)

Not sure if this is pertinent, but each name in "A:A" is separated by
several rows.

Just trying to understand.

--
David

--

Dave Peterson

Hmm... ok. Just can't figure why it wouldn't work here. Oh, well, at least
I've got something that does. Many thanks.

--
David

Dave Peterson wrote

It still worked ok for me.

David wrote:

Dave Peterson wrote

Glad you got it working, but there was a difference in those lines.

"Same error, same line" was "no error, different line" for me.


Maybe just a lack of clarity on my part. Substituted your code for my
original and got Type Mismatch error at new line:
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
instead of at my original line:
For iCtr = LBound(myNames) To UBound(myNames)

Not sure if this is pertinent, but each name in "A:A" is separated by
several rows.

Just trying to understand.

--
David

Dave Peterson wrote

It still worked ok for me.


Could it be that myList comes from a column other than A? The list I select
names from for myList is in AC. The .Find range is in A. I really don't see
what difference that would make, but I'm straw grasping.

--
David

It shouldn't make a difference.

Does this work ok for you?

Option Explicit
Sub testme()

Dim myNames As Variant
Dim iCtr As Long
Dim wks As Worksheet

Set wks = Workbooks.Add(1).Worksheets(1)

With wks
.Name = "PlaceNameHere"
With .Range("ac1:Ac10")
.Formula = "=cell(""address"",ac1)"
.Name = "MyList"
End With
End With

myNames = Worksheets("placenamehere").Range("myList").Value
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
MsgBox myNames(iCtr, 1)
Next iCtr

End Sub

It creates a new workbook and plops some test data into Ac1:ac10 of a test
worksheet.



David wrote:

Dave Peterson wrote

It still worked ok for me.


Could it be that myList comes from a column other than A? The list I select
names from for myList is in AC. The .Find range is in A. I really don't see
what difference that would make, but I'm straw grasping.

--
David

--

Dave Peterson

Yes, it does work.

--
David

Dave Peterson wrote

It shouldn't make a difference.

Does this work ok for you?

Option Explicit
Sub testme()

Dim myNames As Variant
Dim iCtr As Long
Dim wks As Worksheet

Set wks = Workbooks.Add(1).Worksheets(1)

With wks
.Name = "PlaceNameHere"
With .Range("ac1:Ac10")
.Formula = "=cell(""address"",ac1)"
.Name = "MyList"
End With
End With

myNames = Worksheets("placenamehere").Range("myList").Value
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
MsgBox myNames(iCtr, 1)
Next iCtr

End Sub

It creates a new workbook and plops some test data into Ac1:ac10 of a
test worksheet.



David wrote:

Dave Peterson wrote

It still worked ok for me.


Could it be that myList comes from a column other than A? The list I
select names from for myList is in AC. The .Find range is in A. I
really don't see what difference that would make, but I'm straw
grasping.

--
David