Use Named Range instead?

[David]

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?

 

[Dave Peterson]

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]

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 here:
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.

 

[Dave Peterson]

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

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

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 here:
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]

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.

 

[Dave Peterson]

It still worked ok for me.

Dave Peterson wrote


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]

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.

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.

 

[Dave Peterson]

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]

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]

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)

 

[Dave Peterson]

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]

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.

 

[Dave Peterson]

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]

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.

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.

 

[Dave Peterson]

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]

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.

 

[Dave Peterson]

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]

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?

 

[Dave Peterson]

I was confused by this line:

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

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?