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

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

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

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

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

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

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

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

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

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