PDA

View Full Version : Seating problem



shonshon
10-22-2008, 09:19 PM
Hello,
I am using a database to find the seats for the students. The database puts the results in an excel file. For example if I have four students , Monica , albert, chris and amy, the database dump gives me numbers for each one, so
Monica is 1
Chris is 2
Albert is 3
Amy is 4


and it gives me the seat number , the start and the end for example:
seats from 10 to 15 and then it gives me 0.00000.0 , so each zero after the dot is corresponding to one seat.
and then the database put the number corresponding to the students in their seats. for example in 0.00300.0 for seats starting by 10 and end by 15 means that albert (as he is number 3 ) is sitting in seat number 13
and so on
I have a column for so many students and so many seats
I attached a file that describes the problem
Can anyone write a macro so it takes all these data and then give me the student name followed by the seat number so I want Albert13

Thanks
Shon Shon

shonshon
10-24-2008, 07:08 AM
I will really appreciate if anyone can help me with this
Thanks
Shon Shon

georgiboy
10-25-2008, 03:01 AM
Not sure if this is any help as i am not sure how many students you have but it might put you on the right track.

georgiboy
10-25-2008, 05:03 AM
I have now completed a VBA equivelent that i feel works much better, i have also attached the spreadsheet with the code in for you to look at.

Sub SeatPlan()
Dim Per As String, x As Integer
Dim rngRequested As Range, MyRange As Range, rCell As Range
Dim MyRange2 As Range, iCell As Range
Dim LastRowStudents, LastRowSeatPlan As Integer

LastRowStudents = Range("G" & Rows.Count).End(xlUp).Row
LastRowSeatPlan = Range("A" & Rows.Count).End(xlUp).Row

Set MyRange = Sheets("B").Range("D4:D" & LastRowSeatPlan)
Set MyRange2 = Sheets("B").Range("G3:G" & LastRowStudents)


For Each iCell In MyRange2.Cells
Set rngRequested = iCell


On Error Resume Next
For Each rCell In MyRange.Cells

Per = WorksheetFunction.Find(rngRequested.Value, rCell.Offset(, -1).Value) - (3) + (rCell.Offset(, -3).Value)
If WorksheetFunction.Find(rngRequested.Value, rCell.Offset(, -1).Value) < 1 Then GoTo Jump01

With Application.WorksheetFunction
If rCell.Value = "" Then
rCell.Value = .VLookup(rngRequested.Value, Range("G:H"), 2, False) & Per
Else
rCell.Value = rCell.Value & ", " & .VLookup(rngRequested.Value, Range("G:H"), 2, False) & Per
End If
End With


Jump01:
Next rCell
Next iCell
End Sub

What i will say though is if you have a student with the number 10 this will be looked at as student 1 and an empty seat 0, this is why i have given you an example with letters as well.

Hope this solves you problem

shonshon
11-01-2008, 04:37 PM
Thanks Georgiboy,
Actually, your macro is so excellent, but I have a problem that I wasn't able to fix over the past few days.
Some students can sit in more than seat. for example, Chris can sit in seats number 12 and seat number 15 , the problem that when I tried this with your macro it didn't tell me except that Chris can sit in seat number 12.

I will really appreciate if you can fix this problem for me
Thanks
Shon Shon

georgiboy
11-02-2008, 09:14 AM
I have looked and i am afraid i am stuck, hopefuly someone will see this message and be able to ammend my code because i am not afraid to admit this is out of my league. I pushed my knowledge to come up with this. If anyone else could assist i would apreciate it, as it would be part of my learning curve. Thanks in advance.

Bob Phillips
11-02-2008, 10:34 AM
Try this



Public Sub SeatPlan()
Dim PlanRange As Range
Dim StudentRange2 As Range
Dim LastRowStudent As Long
Dim LastRowSeatPlan As Long
Dim LookVal As Variant
Dim Student As String
Dim ResultList As String
Dim cell As Range
Dim i As Long

LastRowStudent = Range("G" & Rows.Count).End(xlUp).Row
LastRowSeatPlan = Range("A" & Rows.Count).End(xlUp).Row

Set PlanRange = Sheets("B").Range("C4:C" & LastRowSeatPlan)
Set StudentRange = Sheets("B").Range("G3:H" & LastRowStudent)

For Each cell In PlanRange.Cells

ResultList = ""
For i = 3 To Len(cell.Value) - 2

If Mid$(cell.Value, i, 1) <> "0" Then

LookVal = Mid$(cell.Value, i, 1)
If IsNumeric(LookVal) Then LookVal = Val(LookVal)
Student = Application.VLookup(LookVal, StudentRange, 2, False)
ResultList = ResultList & ", " & Student & cell.Offset(0, -2).Value + i - 2
End If
Next i
cell.Offset(0, 1).Value = Mid$(ResultList, 3)
Next cell

End Sub

georgiboy
11-02-2008, 10:39 AM
Thanks XLD i will study this and try to learn from it, Do i call you by the name that others call you by or do you prefer XLD?

shonshon
11-02-2008, 10:48 AM
Thanks alot xld , but still another problem, usually it gives me the wrong seat number. the seat that I got corresponding to each student is usually 1 seat after the correct seat.
for example, I have the start number as 10 and ends at 15, and Monica should be in seat number 10 and Chris 15, but I am getting that Monica in seat number 11 and Chris in seat number 16
Please let me know how to fix this
Thanks alot
ShonShon

Bob Phillips
11-02-2008, 11:04 AM
Thanks XLD i will study this and try to learn from it, Do i call you by the name that others call you by or do you prefer XLD?

No, feel free to use my name, I am not hiding.

Bob Phillips
11-02-2008, 11:05 AM
Public Sub SeatPlan()
Dim PlanRange As Range
Dim StudentRange2 As Range
Dim LastRowStudent As Long
Dim LastRowSeatPlan As Long
Dim LookVal As Variant
Dim Student As String
Dim ResultList As String
Dim cell As Range
Dim i As Long

LastRowStudent = Range("G" & Rows.Count).End(xlUp).Row
LastRowSeatPlan = Range("A" & Rows.Count).End(xlUp).Row

Set PlanRange = Sheets("B").Range("C4:C" & LastRowSeatPlan)
Set StudentRange = Sheets("B").Range("G3:H" & LastRowStudent)

For Each cell In PlanRange.Cells

ResultList = ""
For i = 3 To Len(cell.Value) - 2

If Mid$(cell.Value, i, 1) <> "0" Then

LookVal = Mid$(cell.Value, i, 1)
If IsNumeric(LookVal) Then LookVal = Val(LookVal)
Student = Application.VLookup(LookVal, StudentRange, 2, False)
ResultList = ResultList & ", " & Student & cell.Offset(0, -2).Value + i - 3
End If
Next i
cell.Offset(0, 1).Value = Mid$(ResultList, 3)
Next cell

End Sub

shonshon
11-02-2008, 08:50 PM
Thanks alot Xld and Georgiboy
Shon Shon