Yes, I was wondering if this could be visualized. The schedules wsheet
has 7 tables on it, visually, stacked on top of one another (there are
extra rows between tables for white space).

Each table pertains to a school hour, and each column is a different
teacher.

Under a teacher's name is some other general info, such as the class
name (ie. math), grade level, max students allowed, etc.

Then, under a teacher's name is a list of the kids. The tables are
pre-formatted; there are only 20 rows under a teacher's name--no more
than 20 students allowed.

The only data that changes are the student names, which are selected
from the listboxes.

[COLUMN B]

Period #1
--------------
TeacherName
High School
English
Max. No. of Students 15
Actual No. of Students 15
--------------
[RANGE1]
Student1
--------------
Student2
--------------
Student3
--------------
Studentx
--------------

[ROW 40, COLUMN B]
Period #2
--------------
TeacherName (same teacher for all of COLUMN B)
High School
Math
Max. No. of Students 15
Actual No. of Students 15
--------------
[RANGE2]
Student1
--------------
Student2
--------------
Student3
--------------
Studentx
--------------


Hope this helps.

Are there always 7 periods listed for each teacher?

Arnold wrote:
Yes, I was wondering if this could be visualized. The schedules wsheet
has 7 tables on it, visually, stacked on top of one another (there are
extra rows between tables for white space).

Each table pertains to a school hour, and each column is a different
teacher.

Under a teacher's name is some other general info, such as the class
name (ie. math), grade level, max students allowed, etc.

Then, under a teacher's name is a list of the kids. The tables are
pre-formatted; there are only 20 rows under a teacher's name--no more
than 20 students allowed.

The only data that changes are the student names, which are selected
from the listboxes.

[COLUMN B]

Period #1
--------------
TeacherName
High School
English
Max. No. of Students 15
Actual No. of Students 15
--------------
[RANGE1]
Student1
--------------
Student2
--------------
Student3
--------------
Studentx
--------------

[ROW 40, COLUMN B]
Period #2
--------------
TeacherName (same teacher for all of COLUMN B)
High School
Math
Max. No. of Students 15
Actual No. of Students 15
--------------
[RANGE2]
Student1
--------------
Student2
--------------
Student3
--------------
Studentx
--------------


Hope this helps.

Hi Jason,

Yes, each teacher is in every table matrix, and in their respective
column.

However, during each hour, there will be 2 teachers that have plan
time, meaning no students will be selected in their column inside the
range.

I realize this is rather odd, and am also working on a separate dbase,
but we'd like to have this functionality if at all possible in the
mean-time.

Thanks,

Also, are there blank rows between student names within each period? That is
what I took the ------- to mean.

Mike F
"Arnold" wrote in message
ups.com...
Hi Jason,

Yes, each teacher is in every table matrix, and in their respective
column.

However, during each hour, there will be 2 teachers that have plan
time, meaning no students will be selected in their column inside the
range.

I realize this is rather odd, and am also working on a separate dbase,
but we'd like to have this functionality if at all possible in the
mean-time.

Thanks,

Hi Mike

No, no blank rows between student names. Each cell in a class range is
a different student, or null cells (because some classes don't have the
max. no. of students.

And, each cell in a range will be a separate student--no dupes. But,
my ranges span multiple columns, like B15:H31 on the schedules wsheet.

Since I posted this this am, I've been searching posts, looking at
things like the find method, capturing column numbers or letters,
matching cells in ranges, etc. I wish I could draw a picture...

Let me try to re-word our desired result...

In each row of the Student info wsheet, at the end, I'd like to be 7
additional columns--one for each hour of the day. These would be
protected--the cell contents would come from the Schedules wsheet.
Once all the students are input into a range, such as Hour or Period
#2, the user could go back to the Student wsheet and see what class a
particular student would be in for each hour. But, the teachers'
names, given above each range on the Schedules wsheet, would be what
are visible in the 7 columns on the Student wsheet. Basically, we'd
like to quickly see what teacher to call to verify if a student is in
his or her class.

Assume student names list are in a worksheet named Names in column A
starting in A2 and classes will be listed in columns H:I
Assumes schedules are in a sheet named Schedules with instructors in columns
B:K

Each period range has at least the word Period in the first cell the range
in column B (ex: Period #1 or Period #7). the Instructor name is in the
next cell/row below period for each period in the appropriate column.

Option Explicit
Sub PopulateStudents()
Dim v(0 To 7) As Long
Dim teach(1 To 7, 1 To 10) As String
Dim subj(1 To 7, 1 To 10) As String
Dim i As Long, sStr As String, rng As Range
Dim sh As Worksheet, sh1 As Worksheet
Dim rng1 As Range, j As Long, rng2 As Range
Dim sAddr As String, cell As Range
Dim period As Long
v(0) = Rows.Count
Set sh = Worksheets("Schedules")
Set sh1 = Worksheets("Names")
With sh 'Schedules - find Ranges
' assumes the first cell in column B of the range contains
' the word period and there are 7 periods
sStr = "Period"
For i = 1 To 7
Set rng = Nothing
Set rng = .Columns(2).Find(What:=sStr, _
After:=.Range("B" & v(i - 1)), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
v(i) = rng.Row
For j = 1 To 10
teach(i, j) = rng.Offset(1, j - 1).Value

subj(i, j) = rng.Offset(3, j - 1).Value
Next
Else
MsgBox "Period #" & i & " could not be found - quitting"
Exit Sub
End If
Next
End With
With sh1 ' Names
' assumes names are incolumn A starting in A2 and are contiguous
Set rng2 = .Range(.Range("A2"), .Range("A2").End(xlDown))
End With

For Each cell In rng2
sStr = cell.Value
Set rng1 = Nothing
Set rng1 = sh.Cells.Find(What:=sStr, _
After:=sh.Range("IV65536"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
sAddr = rng1.Address
Do
period = 8
For i = 7 To 1 Step -1
If rng1.Row v(i) Then
period = i
Exit For
End If
Next
If period < 8 Then
cell.Offset(0, period).Value = teach(period, rng1.Column - 1)
End If
Set rng1 = sh.Cells.FindNext(rng1)
Loop Until rng1.Address = sAddr
End If
Next
End Sub

worked for me with a sheet matching your description - assumptions in
comments.


--
Regards,
Tom Ogilvy



"Arnold" wrote in message
ups.com...
Hi Jason,

Yes, each teacher is in every table matrix, and in their respective
column.

However, during each hour, there will be 2 teachers that have plan
time, meaning no students will be selected in their column inside the
range.

I realize this is rather odd, and am also working on a separate dbase,
but we'd like to have this functionality if at all possible in the
mean-time.

Thanks,

Hi Tom,

Incredible block of code that you provided. Thanks for assisting. I
saw your name many times as I was researching today.

I must say that I am not a programmer and do not use Excel VBA much. I
revised my wbook to match your assumptions. This is probably basic
programming, but I couldn't get the code to run--the teacher names
would not appear in the period columns on the first wsheet (names).
Should this sub be placed in the workbook VBA or other module; how does
it run? Thanks,

Arnold

It should be place in a general module

Alt+F11, Insert=Module

Then go back to Excel (Alt+F11), go to Tools=Macro=Macros, select the
macro name in the dialog and click Run.

--
Regards,
Tom Ogilvy


"Arnold" wrote in message
ups.com...
Hi Tom,

Incredible block of code that you provided. Thanks for assisting. I
saw your name many times as I was researching today.

I must say that I am not a programmer and do not use Excel VBA much. I
revised my wbook to match your assumptions. This is probably basic
programming, but I couldn't get the code to run--the teacher names
would not appear in the period columns on the first wsheet (names).
Should this sub be placed in the workbook VBA or other module; how does
it run? Thanks,

Arnold