Please take the challenge!!!
Please take the challenge!!!
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Please take the challenge!!!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What exactly do you want to happen during this step?

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone up for the challenge?

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com