• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a macro help

R

ramma556

New Member
Hi,
First of all, I wanted to say, I have learned and always encouraged by you to learn excel more and more. Thank all of you for sharing your valuable knowledge for you knowledge box for people like me and others also.

Coming to the point. I am recently assigned of new role of scheduling movie time. But here I come to know that macro can help me to do this automatically. Currently doing this manually. I need your kind help on this. I tried my best to explain my problem in details but if I missed anything request you kindly update and ask me if anything you required. I have attached a sample file with description.

Thank you in advance because I am very much certain that you guys always find solutions to any problems (means excel problems;) ).
 

Attachments

  • Sample File.XLSX
    12.5 KB · Views: 6
Hi ,

A few questions :

1. How does the macro decide which screen will have which movies ? And how many of them ? For instance you have entered only one movie on screen 1 , while there are 4 movies on screen 3. Why is this so ? Why not 4 movies on screen 1 and 1 movie on screen 3 ?

2. Why is the starting time for the 1st. show on the 3 screens different from one another ? Are these fixed at 9.15 a.m. , 10 a.m. and 11 a.m. ?

3. On screen 2 you are showing two screenings of the same movie one after the other , at 2.45 p.m. and 4.45 p.m. ; on screen 3 , the shows are in rotation in the order E , C , A , D and E once more. On what basis is this decided ?

There may be more , but can you answer these to start with ?

Narayan
 
Thank you for giving your attention.
1. It will be decided by user (means me) that how many movies will be there in one screen. I want this to be manually only.
2. 1st show timings also will be entered manually. It will be decided on situation and requirement so here manual entry is needed.
3. All show timings are based on the situation and the requirement of the moment so it will be decided manually. There are hardly any fixed logic or rules for this because it is on the requirement. If any movies seem to do well it will get maximum shows (and likewise there is many conditions and parameters to decide this).

Above were answered to your query and below is some explanation for my end which I felt is necessary to mention here:
1) I will use the validation for movie selection. Because this will be manually and validation will restrict the chances of spell error. I have shown in sample file 3 different screens and different scenario because in practical no. of shows and no. of movies is not fixed. No. of movies or shows in a screen will be a maximum of 5.
2) In practice I have to enter 1st show time then calculate rest 4 show timings manually as per movies. I want this 4 timings should be automated and will be done in one right click in each cell, means 4 right clicks for 4 timings and this way I will decide for which movie and for which show. But I will click in an order like after entering 1st show manually, I will click for 2nd show, 3rd show, 4th show and 5th show. I want this to be automated because before finalizing and freezing a screen, we change and try different movies at least 10 times or more by changing a screen time and movies. This way there is many screens we have to handle.
3) As there is no fixed logic for this to decide which show time will be where and how many times a movie show time will be, I want this to be done manually. But the calculations of timings should be automated because in this way chances of error will be nothing and it will increase the productivity.

Some more explanation:
For Screen 1- Assuming that I am working with the desired macro then how macro should work:- I will enter manually 9.15 am in Cell E5 then right click in Cell F5 and get result 12.15 pm and then right click in cell G5 and get result 3.15 pm likewise for other cells. As there is no movies in other cells (D3, D4, D6, D7), if I right click in these cells (like F3, F4, F6, F7 or G3, G4, G6, G7), it will not show me any result because there is no movies for these cells.
And if there is a need to enter a one more movie in Screen 1 then I first manually select a movie in Cell D6 and right click in cell F6 then the time of cell F5 (12.15pm) will be removed or cleared and a time will be calculated as per movie of Cell D6 and result will be in cell F6. Other cells (G5, H5, I5) will be changed or refreshed the timings accordingly showtime of cell F6 because a showtime is based on Movie and its preceding show time.


If anything to ask, kindly ask.
 
Hi ,

Thanks for the very detailed explanation ; let me understand it in my own words.

1. Each screen can accommodate up to 5 movies.

2. To start , you will enter the movie in D3 or D8 or D13. Or will it be anywhere in the range D3 through D7 , D8 through D12 , D13 through D17 ?

3. If we assume that a movie has been selected in D3 , then you will enter a time for the first show in E3.

4. The remaining times should appear in F3 , G3 , H3 and I3 according to the duration of the movie which has been selected.

5. Now , suppose you select a second movie on this screen in D4.

6. You can enter a time for this movie's first show anywhere in the range E4 through I4.

7. Suppose you enter the time in G4.

8. The time for the earlier selected movie in G3 should be removed , and the times in H3 and I3 should be appropriately changed based on the duration of the movie in D4.

9. This process should be repeated as and when a new movie is selected anywhere in column D.

Can you confirm the above or correct where ever necessary ?

Narayan
 
Replying point by point:
1) Yes, maximum can be 5.

2) It is not fixed that I always choose 1st row of a screen. Yes, I could use any cell between D3:D7 / D8:D12 / D13:D17. I generally use middle cell if I am sure that I will use only 1 movie in a screen and will not add further movie to just look good the structure.

3) Yes, I want that always 1st show time should be in upper row for 1st movie. If I select or add 2nd movie then its show time should be below 1st movie.

4) Logically yes, if I use Row 3 (means D3) for movie and I will do manual entry in 1st show in row 3 (means E3) and other show timings will go accordingly this. This is assuming that I will use only 1 movie in a screen.
But in practice I will use row 5 (means D5) if I am sure that I willl use only 1 movie and will not add or select any 2nd movie in this screen. This is because to look good the structure.

5) Yes

6) No, if this is 2nd movie in a screen then range will be from F4 through I4 because E4 cannot be filled if E3 is used for showtime. Because in a range (E3:E7/F3:F7/G3:G7/H3:H7/I3:I7) only one cell should be in used.
And yes in the range (F4:I4) I can enter showtime anywhere.

7) Yes

8) Yes, exactly the same.
Just a clarification: Show time of H3 and I3 will refer to the duration of 1st movie (i.e D3) but yes will be revised if I add or change in G4 (because assuming that 2nd movie duration is different from 1st movie).

9) Yes, if any new movie is added and I try to add or change a new show time then this process should refresh or update.

Some Additional point again:
1) As you can see in attachment I have used text type for show timings (e.g. 9.30 am). If it is possible, I want to get the resullt in this format only. Means wherever timings will be used / seen, it will be in same text format not in time format (like 9:30 AM).
2) Assuming that I am working with desired macro then in screen one (as per attachment sent earlier) if I change 1st show time manually (because only 1st showtime will be manually) from 9.15 am to 9.30 am then rest 4 showtimings will change or refresh or update automatically. Means 12.15 pm will be 12.30 pm; 3.15 pm will be 3.30 pm; 6.15 pm will be 6.30 pm and 9.15 pm will be 9.30 pm. This is assuming that I am not adding any 2nd movie in screen 1.

Thank you again Narayan for giving your valuable time to my problem.
 
Hi,
I just wanted to create a app for monitoring my windows machine so just need your help to create an realtime app by using macro and its reports to be generated in Excel and HTML,and also need a dash board for it.The problem is i am new to Macro,can u plz give me a start for creating this thing .
Thanks in advance.
 
Hi ,

Sorry but I have not been able to give any time to your problem over the last few days ; can you wait for some more time ?

Narayan
 
Nothing to Sorry Narayan! At least somebody is noticing my issue and will work on this, this hope is more than enough for me. Thanks again.
 
You must log in or register to reply here.
Back
Top