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

[Arnold]

Hi Programmers,

I have 2 sheets in a wbook, one that lists student names, and the other
that has student schedules.

On the schedules sheet, there are 7 school hours and each is a
different range.

In each range, there are 10 columns (1 column for each teacher and what
they're teaching).

Below the teacher names and other class info starts the list of
students; listboxes and data validation pulling names from the names
wsheet are used to select the students in the classes.

Here's what we'd like: on the names wsheet, add 7 columns to the right
of the student data, with each column being a class hour. The teacher
names (in the first row of each range) for each hour would be displayed
in the appropriate cells for each student. This would provide us with
a quick way to see what class a student is at a certain time of day.

Thanks,

Arnold

 

[Mike Fogleman]

I think we would need an example of how the data is laid out on the schedule
sheet. I get a visual that the hours are listed vertically down perhaps
column A. However, they are contiguous. Hour2 may start 30 rows below Hour1,
allowing for the list of students under each Teacher's name. Does this
describe what you have. Are there blank rows separating the last student in
Hour1 from the first student in Hour2,etc.?
Mike F

 

[Mike Fogleman]

However, they are NOT contiguous.

I think we would need an example of how the data is laid out on the
schedule sheet. I get a visual that the hours are listed vertically down
perhaps column A. However, they are contiguous. Hour2 may start 30 rows
below Hour1, allowing for the list of students under each Teacher's name.
Does this describe what you have. Are there blank rows separating the last
student in Hour1 from the first student in Hour2,etc.?
Mike F

 

[Arnold]

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

 

[Arnold]

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

 

[Jason Lepack]

Are there always 7 periods listed for each teacher?

 

[Arnold]

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,

 

[Mike Fogleman]

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

Mike F

 

[Arnold]

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.

 

[Tom Ogilvy]

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.

 

[Arnold]

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

 

[Tom Ogilvy]

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.

 

[Arnold]

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