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,