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

A

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
 
M

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
 
M

Mike Fogleman

However, they are NOT contiguous.
Mike Fogleman said:
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
Click to expand...
 
A

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
 
A

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
 
A

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,
 
M

Mike Fogleman

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

Mike F
 
A

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.
 
T

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.
 
A

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel ALERT a duplicate of the match of two cells in two different columns... please 0
Randomize tab colors when new sheets are added, and link color to formatting 2
Exporting Values into Microsoft Word Form Fields 2
Application for scheduling students? 1
Combing Student list and Classes they took to one sheet 1
Reverse Vlookup and Arrays 5
Fill cells with names from list 5
Why would a Data Validation Listbox show all records during a Filter? 5

Top