VBA Sub procedure

[mpstorey]

Hey everyone , I was wondering if you help me. I'm having a very difficult time creating a sub procedure for the following problem.

Here it is.


to write a [FONT=Times New Roman,Times New Roman][FONT=Times New Roman,Times New Roman]VBA sub procedure [/FONT][/FONT]called [FONT=Times New Roman,Times New Roman][FONT=Times New Roman,Times New Roman]Timetable [/FONT][/FONT]to help him automate the conversion of his daily schedule to a timetable. Your sub procedure should read information stored in cells A2 to D5 and convert this data into a timetable as shown below. For your sub procedure you may use the following assumption:
-Time is displayed in military format (24 hours)
-You instructor always has 4 schedules per day
-Every meeting would start and end right on the hour (meeting would last at least

Here is a link for what the layout is suppose to be
www.wix.com/mpstorey/Timetable

thanks a lot , your help is very appreciated

 

[mpstorey]

Please anyone.

 

[sous2817]

Must be the start of a new semester...or at least close to exam time. This looks an awful lot like a homework assignment to me.

 

[mpstorey]

Hey, yes its assignment number 3 out of 4 , the prof said it was easy but its hard to go though the processes with him as he teaches. It gets very confusing

 

[sous2817]

ha! well at least you're honest. Most times people try to pass it off as some work assignment their boss just dumped in their laps and they just so happened to phrase it like a school assignment...give me a bit and I'll see if I can hack something together. Maybe someone will come along in the meantime and get you something (but to warn you, most won't knowing it's a homework assignment).

 

[mpstorey]

Thanks a lot! this mean a lot man..I dont need the answer exactly, im just trying to get started. If you can even just help me with the first column and i can try to go from there.

 

[sous2817]

OK, this is how I'd do it:

Sub Timetable()
Dim rTime As Range, rHighlight As Range
Dim lStart As Long, lEnd As Long, i As Long
Dim sStartDate As String, sEndDate As String

Sheet1.Range("B10:C22").Delete

Set rTime = Sheet1.Range("A10:A22")

For i = 2 To 5
sStartDate = Format(Sheet1.Range("B" & i).Value, "h:mm")
sEndDate = Format(Sheet1.Range("C" & i).Value, "h:mm")

lStart = rTime.Find(What:=sStartDate, After:=Sheet1.Range("A10"), LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Row
lEnd = rTime.Find(What:=sEndDate, After:=Sheet1.Range("A10"), LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Row
        
Sheet1.Range("B" & lStart) = Sheet1.Range("A" & i).Value
Sheet1.Range("C" & lStart) = Sheet1.Range("D" & i).Value

Sheet1.Range("B" & lStart & ":C" & lEnd - 1).Interior.ColorIndex = i + 2
    
Next i

End Sub

Here are my assumptions:

1) Grid is on sheet1 of the workbook
2) Timetable grid is on Sheet1!A10 to Sheet1!C22
3) All times are formatted as military time (I used the "h:ss" custom format).

Let me know if you'd like me to email you my test workbook as a reference.

 

[Mathiruban]

hey did you get the answers to this?

 

[Mathiruban]

i tried entering in what sous2817 posted and it didnt work

 

[sous2817]

You'll have to be a bit more specific...what didn't work, or how didn't it work? Seemed to work on my test sheet.

What specifically are you trying to do?