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

Use Named Range instead?
 

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

Use Named Range instead?
 

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

Use Named Range instead?
 

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

Use Named Range instead?
 

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

Use Named Range instead?
 

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

Use Named Range instead?
 

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

Use Named Range instead?
 

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

Use Named Range instead?
 

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

Use Named Range instead?
 

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

Use Named Range instead?
 

Dave Peterson wrote

It shouldn't make a difference.


Ok, I think I've discovered what might be the difference.

I tested this after selecting some CONSECUTIVE names in AC and naming that
selection myList:
Sub PrintMine()
Dim HPB As HPageBreak, FoundCell As Range
Dim myNames As Variant, NumPage As Long, iCtr As Long
myNames = Worksheets("ClassHours").Range("myList").Value
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr, 1))
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
Next iCtr
End Sub

And it worked!!

Problem is MY myList is chosen using Ctrl-click on NONCONSECUTIVE names in
the list and assigning a Name to THOSE selected cells. Then code bombs with
TypeMismatch at
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)

--
David

Use Named Range instead?
 

Ahhhh.

Good debugging!

I didn't think that your range name consisted of multiple areas. So I didn't
even come close to trying it.



David wrote:

Dave Peterson wrote

It shouldn't make a difference.


Ok, I think I've discovered what might be the difference.

I tested this after selecting some CONSECUTIVE names in AC and naming that
selection myList:
Sub PrintMine()
Dim HPB As HPageBreak, FoundCell As Range
Dim myNames As Variant, NumPage As Long, iCtr As Long
myNames = Worksheets("ClassHours").Range("myList").Value
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)
Set FoundCell = Range("A:A").Find(What:=myNames(iCtr, 1))
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
Next iCtr
End Sub

And it worked!!

Problem is MY myList is chosen using Ctrl-click on NONCONSECUTIVE names in
the list and assigning a Name to THOSE selected cells. Then code bombs with
TypeMismatch at
For iCtr = LBound(myNames, 1) To UBound(myNames, 1)

--
David

--

Dave Peterson

Use Named Range instead?
 

Dave Peterson wrote

Ahhhh.
Good debugging!

Thanks.

I didn't think that your range name consisted of multiple areas. So I
didn't even come close to trying it.

Guilty again of leaving out a pertinent bit of info. :(

The desire/need arose to print pages only for the patients on my caseload
from a list of all patients in our program. Hence: Sub PrintMine()

I was seeking a way to not have to enter those names manually into an array
in the subroutine. A named range seemed the way to go.

Thanks for hanging with me through this.

--
David

Use Named Range instead?
 

If you happen to add more rows/sections/names, using the named range may make it
a bit cumbersome.

If you had some indicator that said that this was a name to use (maybe the only
values in the column are all the names you need???).

You could loop through that range and process the cell if its non-empty.



David wrote:

Dave Peterson wrote

Ahhhh.
Good debugging!

Thanks.

I didn't think that your range name consisted of multiple areas. So I
didn't even come close to trying it.

Guilty again of leaving out a pertinent bit of info. :(

The desire/need arose to print pages only for the patients on my caseload
from a list of all patients in our program. Hence: Sub PrintMine()

I was seeking a way to not have to enter those names manually into an array
in the subroutine. A named range seemed the way to go.

Thanks for hanging with me through this.

--
David

--

Dave Peterson

Use Named Range instead?
 

Answers in line.

Dave Peterson wrote

If you happen to add more rows/sections/names, using the named range
may make it a bit cumbersome.

Hmm... unintended consequence. Just tested by adding a patient to the
workbook. Selected myList from the Name Box and it had shuffled my
caseload! which means even though my caseload didn't change, I would have
to reselect those names, Delete and re-Insert the myList range name. Ouch.
I'll have to revert to hard-coding my caseload into PrintMine() <sigh

If you had some indicator that said that this was a name to use (maybe
the only values in the column are all the names you need???).

You could loop through that range and process the cell if its
non-empty.

Not grasping what you're proposing, but at this point, I can't (or don't
want to) physically alter the arrangement/format/location of the name list
because other code snippets and Data Validation depend on it. I've spent
over a year developing and refining this workbook already. Present methods
for data handling have actually become quite intricate.


--
David

Use Named Range instead?
 

I was thinking that if the names only appeared in column A (and nothing else),
you could use:

dim myRng as range
dim myCell as range
with worksheets("whatever")
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
if isempty(mycell.value) then
'do nothing
else
Set FoundCell = Range("A:A").Find(What:=mycell.value....
'rest of code...
end if
next mycell

=============
If those names can appear multiple times (so you have duplicates), but nothing
else is in those cells, you could use the technique at John Walkenbach's to get
a list of unique names:

http://j-walk.com/ss/excel/tips/tip47.htm

Kind of...

dim myRng as range
dim myCell as range
dim NoDupes as collection
dim iCtr as long

Set NoDupes = New Collection

with worksheets("whatever")
set myrng = .range("a1",.cells(.rows.count,"A").end(xlup))
end with

for each mycell in myrng.cells
if isempty(mycell.value) then
'do nothing
else
On Error Resume Next
For Each myCell In myrng.Cells
NoDupes.Add myCell.Value, CStr(myCell.Value)
Next myCell
On Error GoTo 0
end if
next mycell

if nodupes.count 0 then
for ictr = 1 to nodupes.count
Set FoundCell = Range("A:A").Find(What:=nodupes(ictr)...
'rest of code...
next ictr
end if

=======
Watch out for typos--I typed it into the message. I didn't check it for errors.

Using the data to determine the list seems a little more robust to me.
Especially if it works!


David wrote:

Answers in line.

Dave Peterson wrote

If you happen to add more rows/sections/names, using the named range
may make it a bit cumbersome.

Hmm... unintended consequence. Just tested by adding a patient to the
workbook. Selected myList from the Name Box and it had shuffled my
caseload! which means even though my caseload didn't change, I would have
to reselect those names, Delete and re-Insert the myList range name. Ouch.
I'll have to revert to hard-coding my caseload into PrintMine() <sigh

If you had some indicator that said that this was a name to use (maybe
the only values in the column are all the names you need???).

You could loop through that range and process the cell if its
non-empty.

Not grasping what you're proposing, but at this point, I can't (or don't
want to) physically alter the arrangement/format/location of the name list
because other code snippets and Data Validation depend on it. I've spent
over a year developing and refining this workbook already. Present methods
for data handling have actually become quite intricate.

--
David

--

Dave Peterson

Use Named Range instead?
 

Dave Peterson wrote

I was thinking that if the names only appeared in column A (and
nothing else),

Not pheasible. Column A contains all the names all right, but each is
followed by a group of classes and a Total -- no empty cells. And it's
there where horizontal page breaks (HPB's) in PrintMine() come into play.

PrintMine() scans column A until it finds a name from my caseload and then
prints that page.

Column AC is the one with names only, no blanks, sorted alphabetically when
added via an AddPatient() routine.

Column AC was not originally intended for use in PrintMine(), but a list
for Data Validation. But I thought I might use it also as a source for a
range name holding my caseload.

But as you pointed out (and I'm glad you did before I celebrated too much),
this workbook is often updated as patients join and leave the program.

My caseload doesn't change THAT often, so changing hard-coded names in
PrintMine() isn't THAT big a deal. For now I've reverted to that and
abandoned the range name approach.

--
David

Use Named Range instead?
 

I'm confused about column AC.

You defined a range name based on discontiguous cells in column A. But in this
message, you say that AC is a single list with no blanks (and no duplicates)?

If it's really a simple list that can grow or contract--no blanks, no
duplicates--you could use a dynamic name that grows and contracts with the
amount of data in that column.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Then you could still use that name in your code and loop through each cell--or
pick it up as an array and loop through the elements of the array.

Take a look at the dynamic range name on Debra's site. You may still be
celebrating.

David wrote:

Dave Peterson wrote

I was thinking that if the names only appeared in column A (and
nothing else),

Not pheasible. Column A contains all the names all right, but each is
followed by a group of classes and a Total -- no empty cells. And it's
there where horizontal page breaks (HPB's) in PrintMine() come into play.

PrintMine() scans column A until it finds a name from my caseload and then
prints that page.

Column AC is the one with names only, no blanks, sorted alphabetically when
added via an AddPatient() routine.

Column AC was not originally intended for use in PrintMine(), but a list
for Data Validation. But I thought I might use it also as a source for a
range name holding my caseload.

But as you pointed out (and I'm glad you did before I celebrated too much),
this workbook is often updated as patients join and leave the program.

My caseload doesn't change THAT often, so changing hard-coded names in
PrintMine() isn't THAT big a deal. For now I've reverted to that and
abandoned the range name approach.

--
David

--

Dave Peterson

Use Named Range instead?
 

Responses in line:

Dave Peterson wrote

I'm confused about column AC.

You defined a range name based on discontiguous cells in column A.
But in this message, you say that AC is a single list with no blanks
(and no duplicates)?

No. Range name myList is based on discontiguous cells in column AC.
PrintMine() finds those names in column A.

If it's really a simple list that can grow or contract--no blanks, no
duplicates--you could use a dynamic name that grows and contracts with
the amount of data in that column.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Then you could still use that name in your code and loop through each
cell--or pick it up as an array and loop through the elements of the
array.

Take a look at the dynamic range name on Debra's site. You may still
be celebrating.


I've seen Debra's site. Dynamic range name formulas appear to refer to
all cells in a column (with maybe an allowance for starting row in the
2nd argument) - no accommodation for discontiguous cells within that
column.

Am I missing something?

--
David

David wrote:

Dave Peterson wrote

I was thinking that if the names only appeared in column A (and
nothing else),

Not pheasible. Column A contains all the names all right, but each is
followed by a group of classes and a Total -- no empty cells. And
it's there where horizontal page breaks (HPB's) in PrintMine() come
into play.

PrintMine() scans column A until it finds a name from my caseload and
then prints that page.

Column AC is the one with names only, no blanks, sorted
alphabetically when added via an AddPatient() routine.

Column AC was not originally intended for use in PrintMine(), but a
list for Data Validation. But I thought I might use it also as a
source for a range name holding my caseload.

But as you pointed out (and I'm glad you did before I celebrated too
much), this workbook is often updated as patients join and leave the
program.

My caseload doesn't change THAT often, so changing hard-coded names
in PrintMine() isn't THAT big a deal. For now I've reverted to that
and abandoned the range name approach.

--
David

Use Named Range instead?
 

I was confused by this line:

Column AC is the one with names only, no blanks, sorted
alphabetically when added via an AddPatient() routine.

There was a disconnect between the no blanks in one of your posts and the
discontiguous range in others.

So is there anything else in Column AC except for each name and empty cells?

If no, you could still pickup that range and loop through them:

dim myListRange as range
dim myCell as range
with whateversheet
set mylistrange = .range("ac1",.cells(.rows.count,"AC").end(xlup)
end with

for each mycell in mylistrange.cells
if isempty(mycell.value) then
'do nothing
else
'do the find
end if
next mycell



David wrote:

Responses in line:

Dave Peterson wrote

I'm confused about column AC.

You defined a range name based on discontiguous cells in column A.
But in this message, you say that AC is a single list with no blanks
(and no duplicates)?

No. Range name myList is based on discontiguous cells in column AC.
PrintMine() finds those names in column A.

If it's really a simple list that can grow or contract--no blanks, no
duplicates--you could use a dynamic name that grows and contracts with
the amount of data in that column.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Then you could still use that name in your code and loop through each
cell--or pick it up as an array and loop through the elements of the
array.

Take a look at the dynamic range name on Debra's site. You may still
be celebrating.


I've seen Debra's site. Dynamic range name formulas appear to refer to
all cells in a column (with maybe an allowance for starting row in the
2nd argument) - no accommodation for discontiguous cells within that
column.

Am I missing something?

--
David

David wrote:

Dave Peterson wrote

I was thinking that if the names only appeared in column A (and
nothing else),

Not pheasible. Column A contains all the names all right, but each is
followed by a group of classes and a Total -- no empty cells. And
it's there where horizontal page breaks (HPB's) in PrintMine() come
into play.

PrintMine() scans column A until it finds a name from my caseload and
then prints that page.

Column AC is the one with names only, no blanks, sorted
alphabetically when added via an AddPatient() routine.

Column AC was not originally intended for use in PrintMine(), but a
list for Data Validation. But I thought I might use it also as a
source for a range name holding my caseload.

But as you pointed out (and I'm glad you did before I celebrated too
much), this workbook is often updated as patients join and leave the
program.

My caseload doesn't change THAT often, so changing hard-coded names
in PrintMine() isn't THAT big a deal. For now I've reverted to that
and abandoned the range name approach.

--
David


--

Dave Peterson

Use Named Range instead?
 

The loop I want wouldn't cycle through the whole list (all the patients
enrolled in our program) in AC, but only the names I select (my caseload)
from AC and apply a range name (myList) to. For each name in my caseload
(myList), PrintMine() would find only those names in A and print the
pages for only those patients.

Example:
ColAC
Name1 through Name100 (names only, no blanks, sorted alphabetically)
I select Name3, Name7, Name8, Name15, and Name21 (the discontiguous
range--my caseload) and apply a range name (myList) to that selection

ColA
Name1 followed by a list of classes
Name2 followed by a list of classes
Name3 followed by a list of classes
and so on through Name100

Each patient's page is set via horizontal page breaks

PrintMine() would loop through myList, finding each of those patients in
ColA and printing that patient's page.


Sorry this has been such a confusing exercise. I know you can't see my
screen.

--
David

Dave Peterson wrote

I was confused by this line:

Column AC is the one with names only, no blanks, sorted
alphabetically when added via an AddPatient() routine.

There was a disconnect between the no blanks in one of your posts and
the discontiguous range in others.

So is there anything else in Column AC except for each name and empty
cells?

If no, you could still pickup that range and loop through them:

dim myListRange as range
dim myCell as range
with whateversheet
set mylistrange = .range("ac1",.cells(.rows.count,"AC").end(xlup)
end with

for each mycell in mylistrange.cells
if isempty(mycell.value) then
'do nothing
else
'do the find
end if
next mycell

Use Named Range instead?
 

Is there anything on that row that indicates that the name in column AC should
be used?

You could check for that indicator, too.

If only you (as a human) know what's in that list, maybe you could use another
workbook to create the list and cycle though that. It might be easier than
fiddling with a range name that changes.

David wrote:

The loop I want wouldn't cycle through the whole list (all the patients
enrolled in our program) in AC, but only the names I select (my caseload)
from AC and apply a range name (myList) to. For each name in my caseload
(myList), PrintMine() would find only those names in A and print the
pages for only those patients.

Example:
ColAC
Name1 through Name100 (names only, no blanks, sorted alphabetically)
I select Name3, Name7, Name8, Name15, and Name21 (the discontiguous
range--my caseload) and apply a range name (myList) to that selection

ColA
Name1 followed by a list of classes
Name2 followed by a list of classes
Name3 followed by a list of classes
and so on through Name100

Each patient's page is set via horizontal page breaks

PrintMine() would loop through myList, finding each of those patients in
ColA and printing that patient's page.

Sorry this has been such a confusing exercise. I know you can't see my
screen.

--
David

Dave Peterson wrote

I was confused by this line:

Column AC is the one with names only, no blanks, sorted
alphabetically when added via an AddPatient() routine.

There was a disconnect between the no blanks in one of your posts and
the discontiguous range in others.

So is there anything else in Column AC except for each name and empty
cells?

If no, you could still pickup that range and loop through them:

dim myListRange as range
dim myCell as range
with whateversheet
set mylistrange = .range("ac1",.cells(.rows.count,"AC").end(xlup)
end with

for each mycell in mylistrange.cells
if isempty(mycell.value) then
'do nothing
else
'do the find
end if
next mycell

--

Dave Peterson

Use Named Range instead?
 

Dave Peterson wrote

Is there anything on that row that indicates that the name in column
AC should be used?

Nope

If only you (as a human) know what's in that list, maybe you could use
another workbook to create the list and cycle though that. It might
be easier than fiddling with a range name that changes.

I think I'd rather opt to copy my caseload names from AC to another column,
Hide it, Name and cycle through that range. I could add/remove names
to/from that list manually

--
David

Use Named Range instead?
 

I'd even put it on another worksheet--then inserting/deleting rows would be
safer.

But if you don't have enough control over that workbook, you can still have your
macro use a completely different workbook, too.

David wrote:

Dave Peterson wrote

Is there anything on that row that indicates that the name in column
AC should be used?

Nope

If only you (as a human) know what's in that list, maybe you could use
another workbook to create the list and cycle though that. It might
be easier than fiddling with a range name that changes.

I think I'd rather opt to copy my caseload names from AC to another column,
Hide it, Name and cycle through that range. I could add/remove names
to/from that list manually

--
David

--

Dave Peterson

Use Named Range instead?
 

I just realized adding/removing classes would indeed disturb my list.
Thanks for making me think.

I really appreciate all the time and effort you've expended on my behalf.

--
David

Dave Peterson wrote

I'd even put it on another worksheet--then inserting/deleting rows
would be safer.

But if you don't have enough control over that workbook, you can still
have your macro use a completely different workbook, too.

Use Named Range instead?
 

Good luck on the project.

David wrote:

I just realized adding/removing classes would indeed disturb my list.
Thanks for making me think.

I really appreciate all the time and effort you've expended on my behalf.

--
David

Dave Peterson wrote

I'd even put it on another worksheet--then inserting/deleting rows
would be safer.

But if you don't have enough control over that workbook, you can still
have your macro use a completely different workbook, too.


--

Dave Peterson