Please take the challenge!!!

[dan2]

Here is a hard one (I think!) to challenge someone.

I have a table of quantity of ppl by half hour of a day as in:

11:00 3
11:30 5
12:00 4
12:30 6
and so on.....

and need to show it pictorally (not as a graph!) as follows:

11:00..11....11....11
11:30..11.5..11.5..11.5..11.5..11.5
12:00..12....12....12....12
12:30..12.5..12.5..12.5..12.5..12.5..12.5
and so on.......

This does make it look like a bar graph I know.
This part I have managed to do with IF statements and some VBA, it is the next stage that at the moment users are having to do manually and it would be alot quicker and easier if I could find a way to automate the last step or even miss out the above step and go straight to the last below

11:00..11....11....11
11.30..11.5..11.5..11.5..11.5..11.5
12.00........12....12....12....12
12.30........12.5..12.5..12.5..12.5..12.5..12.5
and so on......

Im not sure how the above will look on this message or if I have made any sense so if you can't understand it and you think you might be able to help I can e-mail an example to you.

Thanks in advance

Dan.

[Al Chara]

What exactly do you want to happen during this step?

[dan2]

its very hard to explain but basically the bars in the bar graph style pictoralisation of the table are slid along to make a schedule of shifts for staff.

It is very hard to explain in words so I can send an example on a worksheet.

[Al Chara]

I get it, you want the time slots to lineup in their respective time location underneath each other. What are your time limits?

[ This Message was edited by: Al Chara on 2002-04-01 08:13 ]

[dan2]

Al

Ok Ill try and describe the drag and drop process in words (This will be fun!)

Using the figures in the first post, and understanding that the table has col A as time and Col B as qty of ppl, so the amount of ppl the user can have working for each half hour of the day.

The second part basically makes a bar graph of the table with each row being a half hour segment of the day and each col being a amount of ppl.

The finished product needs to be a schedule with each row being time as before and each col will have a name put to it so that it shows that persons shift to work below. The drag and drop process is as follows:

The first row with ppl in it stays where it is, then as long as the next row has the same or more people in it then it also stays where it is. If the next row has less ppl in it then it has to be dragged to the right so that the right hand end of the row is in line with the right hand end of row above. Then after one of the rows has moved then 1 of 2 actions is applied to each row there after either as above if it is shorter than the row above or if it is longer than the row above then it is dragged to the right until the left hand end if level with the left hand end of the row above. and so on until all the rows are moved.

The time goes from 8:00 till 03:00 so there are 39 rows in total.

After reading the above Im not sure if this helps at all to explain what I am trying to do, but I cant think of any better way of putting it into words.

Dan.

[dan2]

Anyone up for the challenge?

[Fryer Tuck]

I set up a test, with times starting at cell A4, and ppl starting in cell B4. If you start on a different line, change the value of "Rwr". Note that with the following, it is important that the cell above the first ppl (in this case that would be cell B3) be left blank or equal to 0. The "bar graph" started in cell D. You could change it to cell "C" by changing the value of Strt to "2", but you can not start it in cell "B".

Run the following in a macro once you have the times and ppl amounts.

Sub Macro1()
Dim Strt, Fnsh, Rwr, FilCnt As Integer
Strt = 3
Rwr = 4
Do While Range("A" & Rwr) <> Empty
If Range("B" & Rwr) > Range("B" & Rwr - 1) Then
Fnsh = Strt + Range("B" & Rwr) - 1
End If
If Range("B" & Rwr) < Range("B" & Rwr - 1) Then
Strt = Fnsh - Range("B" & Rwr) + 1
End If
For FilCnt = Strt To Fnsh
Range("A" & Rwr).Offset(, FilCnt) = Range("B" & Rwr)
Next
Rwr = Rwr + 1
Loop
End Sub

If you run this daily on the same sheet, then you will want to add code that gets rid of the prior day's graph. Also, if you want to get fancy, you can have the cells color or other fancy stuff in the "FilCnt" loop.

Happy scheduling

[dan2]

Fryer Tuck

Thanks alot that has been bugging me for sooooooo long, and has cut a large amount of code out of the modules.

Thanks again

Dan.