Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complex--don't even know how to word the subject for this
Thanks Tom and others. I appreciate the interest and expertise you
provided. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
No Subject | Excel Worksheet Functions | |||
Complex--don't even know how to word the subject for this | Excel Programming | |||
Complex Index Match Help (or at least complex to me) | Excel Discussion (Misc queries) | |||
how do i move from excell to word for better readable subject | Excel Discussion (Misc queries) |