Time Chart from Data

[Rishek]

Going to mark this as solved. Because it is. Big time. If I have further questions, I'll start a new thread and be specific! Again, cheers. If I can just solve the last few implementation quirks, this is a huge help. It's picking up errors really well and the chart looks great when I get it.

Thanks again!

[mdmackillop]

When I looked at the exported data there seemed some name errors. Try doing a search and replace of Para marks with ", Para" and "(" with ", (" These change=s need not be saved in the Word Doc.

[Rishek]

I run the format to extract script before hand which fixes most of these types of things. Or did you mean in the code itself?

[Rishek]

So I'm getting a '1004' error for the row function '_Global' when I try to run Create Schedules from the excel file.

The data seems fine, there are no blank boxes. Debug points me to the GetFormat () sub. Maybe separating out the Schedule Format book was a bridge too far?

[mdmackillop]

Re post 14; If you search Snape in the Data worksheet you'll see it comes after Blinkley. This is because of the Para separation. The Word code needs a comma.

Re post 24; Is ScheduleFormat in the same folder? Try this simplified code to test for a result.

Sub GetFormats2()
Set wbF = Workbooks.Open(ActiveWorkbook.Path & "\ScheduleFormat.xlsx")
MsgBox wbF.Name
End Sub

We can also try building in a "delay"

Sub GetFormats()
Dim i
Set wbF = Workbooks.Open(ActiveWorkbook.Path & "\ScheduleFormat.xlsx")
For i = 1 To 100
DoEvents
Next
ActiveWindow.Visible = False
With wbF.Sheets("Format")
    Set rFormat = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
End Sub

[Rishek]

I have solved the parentheses problem differently using the FormattoExtract macro to delete and alter the formatting so the names are extracted correctly.

Had some issues today, but got as far as a timesplit with no conflicts.

Schedule 5_Jun.xlsm
ScheduleFormat.xlsx

I can't get the schedules macro to work. Either some of the data is bad, but I haven't noticed or it may be an issue with events that start at the same time that the previous event has ended?

Thoughts appreciated.

[mdmackillop]

This processes a copy of the Word table within excel if you want an alternative to try. Maybe e a little simpler to adjust for corrections.

[Rishek]

To be honest, the extraction is working pretty well and the workflow is pretty set for the time being. I'm not sure why the data I attached above isn't working for making schedules. I suspect it has to do with redundant locations possible (WH can be used by more than one person and event at the same time) at least for the locations grid, but it typically errors out before it even gets that far.

I'm now hoping to add yet another check to the timesplit macro to insert an I column: =IF(AND(A2=A3,D2<>D3,F2=E3),"Travel Time!","")

Basically, if a given person ($A) has an event that is not in the same column that is not at the same location ($D), they are (with a few exceptions) going to have a problem if there is not space between the end time of the previous event ($F) and the start time ($E). Similar to the conflict finding macro, but possibly best to put it in yellow. I haven't parsed how to translate this into VBA yet.

Any help as always, appreciated.

[mdmackillop]

Re Post #26; XER was missing from Schedule format.
Replace the Events code with this which will apply a black fill with white text (first three characters) to the missing items

'Event
        Set Fnd = rFormat.Find(Evnt)
        If Not Fnd Is Nothing Then
            Cells(c.Row, S).Resize(, E - S + 1).Interior.ColorIndex = Fnd.Offset(, 1).Interior.ColorIndex
            If E - S > 1 Then Cells(c.Row, S + 1).Resize(, E - S - 1) = Fnd.Offset(, 2)
        Else
            Cells(c.Row, S).Resize(, E - S + 1).Interior.ColorIndex = 1
            If E - S > 1 Then Cells(c.Row, S + 1).Resize(, E - S - 1) = Left(Evnt, 3)
            If E - S > 1 Then Cells(c.Row, S + 1).Resize(, E - S - 1).Font.ColorIndex = 2
        End If

Re Post #28: add the H2 & H1 lines as shown to highlight Travel time issues

With .Range("G2").Resize(Lastrow - 1)
            .FormulaR1C1 = "=IF(RC1<>R[1]C1,SUMPRODUCT(--(R2C1:R" & Lastrow & "C1=RC1),IF(R2C6:R" & Lastrow & "C6="""",1,R2C6:R" & Lastrow & "C6)-R2C5:R" & Lastrow & "C5),"""")"
             '.FormulaR1C1 = "=IF(RC1<>R[1]C1,SUMPRODUCT(--(R2C1:R137C1=RC1),IF(R2C6:R137C6="""",1,R2C6:R137C6)-R2C5:R137C5),"""")"
            .NumberFormat = "hh:mm"
        End With
        .Range("H2").Resize(Lastrow).FormulaR1C1 = "=IF(AND(RC[-7]=R[1]C[-7],RC[-4]<>R[1]C[-4],RC[-2]=R[1]C[-3]),""Travel Time!"","""")"
        .Range("H1").FormulaR1C1 = "=IF(COUNTIF(R[1]C:R[8]C,""Travel time!"")>0,""Travel Time Issues"","""")"
    End With

[Rishek]

Cheers. Sorry this has taken so much back and forth. Will implement this all today.

After that comes the streamlining ...

[mdmackillop]

Cheers. Sorry this has taken so much back and forth. Will implement this all today.

After that comes the streamlining ...

As XLD says
Nihil simul inventum est et perfectum

[Rishek]

So everything is working quite well.

Haven't quite sorted the travel time item, but I will (it's not a priority and once I'm at work, there's actually not a lot of time to test things).

General points:

1) The crosscheck is great, but I need to figure out a way to account for people whose names do NOT appear on the schedule, i.e. have NO commitments. For now, I'll just be copying a list of those I need to check onto the extracted data and giving them a commitment that takes no time.

2) I will probably have to perform a separate cross check with the locations. I did manage to modify the original code to do this. Similarly, I need to be able to check what spots have NO people in them, but this is a lot easier.

3) As long as nobody and nowhere is double booked, I actually only need to extract certain names and locations for the chart. This might printing the damn thing a might easier (currently I'm deleting the dinner hour and doing some creative rescaling, but legibility is a factor).

4) Haven't got the Travel time addition up and running yet. Rather a lot running across my plate at the moment.

Would like to note: we haven't had a double booking since I started doing this.

[Rishek]

So I'm trying to get the travel time issues to appear in yellow in column D. I'm trying to add this code to the HighlightProblems macro:

    With ws.Columns("D:D")
    .FormatConditionsAdd Type:=xlExpression, Forumula1:="AND($A1=$A2,$D1<>$D2,$F1=$E2)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ColorIndex = 6
            .TintAndShade = 0
        End With
    Range("D1:D1").FormatConditions.Delete
    End With

But it does not like that one bit. Any advice? I actually don't need the text in column H or I as long as the errors are highlighted.

[mdmackillop]

Forumula1:

Pulled together.

[Rishek]

Thanks. Played around with this a whole bunch until I realized the formula was switched up: $F1>=$E2, not <=. Now works great.