Complex--don't even know how to word the subject for this
 

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.

Complex--don't even know how to word the subject for this
 

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.

Complex--don't even know how to word the subject for this
 

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,

Complex--don't even know how to word the subject for this
 

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,

Complex--don't even know how to word the subject for this
 

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.

Complex--don't even know how to word the subject for this
 

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,

Complex--don't even know how to word the subject for this
 

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

Complex--don't even know how to word the subject for this
 

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

Complex--don't even know how to word the subject for this
 

Thanks Tom and others. I appreciate the interest and expertise you
provided.