Need help with Macro to run a simulation

[Jack-P-Winner]

I am at a lost even where to begin with this Macro and not sure how complex it will be. I am sure for some of ya'll it will be easy. I want the Game sheet to run a simulation like this:

Place a E in I:3 - I:7,I:11-I:15, I:19-I:23 and I:29, I:32, I:35
Place a U in T and AE with the same locations above (U:3 - U:7,U:11-U:15, U:19-U:23 and U:29,U:32, U:35) and (AE:3 - AE:7,AE:11-AE:15, AE:19-AE:23 and AE:29,AE:32, AE:35)

After these letters (E and Us) are placed in these locations it will leave some numbers under AE2:72, I want to take the numbers it leaves and place them on a new sheet under a column I will label B1

Then remove the Es in all the locations in column I and place the letter O in column J. It will leave different numbers in column AE2:72. Paste these on the new sheet under column labeled B1

Next replace one of the Us at a time with the letter D and see what numbers are left in AE to paste on the new under column labeled B1

I want to follow the sheet attached mark generator as far as the simulation. If I need to explain each location like I did above I can but I think this attachment will help. The only thing it is missing is the Es and Os. I want to run the sheet labeled up/down generator with a E and then With a O in columns I and J

Some simulations will not leave any numbers which is fine too. Is this even possible to do?

 

[NARAYANK991]

Hi Larry ,

The reference AE2:72 is wrong ; when I finish entering Es and Us , there is no number visible in the range AI2:AM72

When I finish entering the Os and Ds , the number 13 is visible in cells AJ13 , AK12 and AL11.

Please confirm whether this is correct.

Secondly , the visibility of the numbers is being done through CF rules , and identifying this through VBA is difficult. If the same CF rules are entered in helper columns to put either 1 or 0 , then the code can look at the values in these helper columns and act accordingly.

Please comment.

Narayan

 

[NARAYANK991]

Hi Larry ,

With the ranges defined using names , the macros are simple ; see if this is OK.

Narayan

 

[Jack-P-Winner]

Hi Larry ,

With the ranges defined using names , the macros are simple ; see if this is OK.

Narayan

Yes this is what I am looking for but a start. What do you need from me beside what I have given? I can list all the combinations with columns and rows from the sheet called Up/Down Generator if that will help
I also want whatever numbers are left in columns AI:AN pasted on a new sheet so I know after each sequence what numbers are left.

 

[Jack-P-Winner]

I basically want to run every combination it can be with this Macro. I will attach 6 pics to see how it will move

 

[Jack-P-Winner]

This takes us through one simulation. The UP down generator sheet has all the combinations as far as I can tell

 

[Jack-P-Winner]

Hi Larry ,

The reference AE2:72 is wrong ; when I finish entering Es and Us , there is no number visible in the range AI2:AM72

When I finish entering the Os and Ds , the number 13 is visible in cells AJ13 , AK12 and AL11.

Please confirm whether this is correct.

Secondly , the visibility of the numbers is being done through CF rules , and identifying this through VBA is difficult. If the same CF rules are entered in helper columns to put either 1 or 0 , then the code can look at the values in these helper columns and act accordingly.


Please comment.

Narayan

You will not always have a number present. It is looking at several things and will place whatever numbers will be available for this at this time

 

[Jack-P-Winner]

These are all the combinations as far as I know for EACH #1 #2 #3 #4 #5 and bellows #1 #1 #1. The only thing I do not have listed is the Es and Os

DDDDDD
DDDDDU
DDDDUD
DDDUDD
DDDUDU
DDDUUU
DDUDDD
DDUDUD
DDUDUU
DDUUUU
DUDDDD
DUDDDU
DUDDUD
DUDUDD
DUDUDU
DUDUUD
DUDUUU
DUUDDD
DUUDDU
DUUDUD
DUUDUU
DUUUDD
DUUUDU
DUUUUD
DUUUUU
UUDDDD
UUDDUD
UUDUDU
UUDUUU
UUUDUD
UUUDUU
UUUUDU
UUUUUU

 

[NARAYANK991]

Hi Larry ,

I need you to do the following :

1. Confirm the range which is to be checked for the numbers remaining visible ; the columns AI through AM have numbers in the range AI2:AM72 , but the column AN has numbers only in the range AN2:AN16. Is this correct ?

2. You have used the words sequence , combination , simulation ; please do not use different words at different times. Can you define precisely what is the meaning of each of these words ? Do they all mean the same , or do they have different meanings ?

3. How is the output to be taken to a different worksheet ; suppose one step is over and a few numbers are left ; where on the separate worksheet will these numbers be pasted ? If we take one example , suppose the number 13 is remaining in cells AJ13 , AK12 and AL11 ; where will these be pasted on the separate worksheet ? How will this output be identified ? On the next step , either the same cells may be visible , or some other cells might be visible ; where will this new set of cells be pasted ?

4. How do we resolve the problem of identifying CF cells ? Is my suggestion of using helper columns acceptable ?

Narayan

 

[Jack-P-Winner]

Hi Larry ,

I need you to do the following :

1. Confirm the range which is to be checked for the numbers remaining visible ; the columns AI through AM have numbers in the range AI2:AM72 , but the column AN has numbers only in the range AN2:AN16. Is this correct ?YES this is correct

2. You have used the words sequence , combination , simulation ; please do not use different words at different times. Can you define precisely what is the meaning of each of these words ? Do they all mean the same , or do they have different meanings ?
Sorry , they all mean the same thing ):

3. How is the output to be taken to a different worksheet ; suppose one step is over and a few numbers are left ; where on the separate worksheet will these numbers be pasted ? If we take one example , suppose the number 13 is remaining in cells AJ13 , AK12 and AL11 ; where will these be pasted on the separate worksheet ? How will this output be identified ? On the next step , either the same cells may be visible , or some other cells might be visible ; where will this new set of cells be pasted ?

1. Can the Macro run through all the UDEOs and place all the numbers it finds in one column labeled B1 B2 B3 B4 B5 BB?
2. Or, have it run and place on a new sheet the combinations ran and the answers it finds
Either way is fine with me even if they have to all go on a different new sheet. Option 1 would be best for me but I also want to take some stress off of you so how ever you want is fine with me

4. How do we resolve the problem of identifying CF cells ? Is my suggestion of using helper columns acceptable ?

Yes your suggestion would work just fine. That is how I did the UP/Down Generator sheet

Narayan

Most importantly THANK YOU!!!!!!!

 

[Jack-P-Winner]

Generator sheet

 

[Hui]

This could all be done with a data table
But your explanation of how / what goes into each cell is terrible and where the which range is the answer is worse.

If you can explain how the
DDDDDD
DDDDDU
DDDDUD
DDDUDD
DDDUDU
relates to the input ranges and which range is the output I'm glad to setup a data table to solve that

 

[Jack-P-Winner]

This could all be done with a data table
But your explanation of how / what goes into each cell is terrible and where the which range is the answer is worse.

If you can explain how the
DDDDDD
DDDDDU
DDDDUD
DDDUDD
DDDUDU
relates to the input ranges and which range is the output I'm glad to setup a data table to solve that

Your correct on your last statement
relates to the input ranges and which range is the output Everything else is already programmed in the CF. I've placed in the generator sheet where each letter goes starting with the first row of 5 letters stepping down. Thanks for chiming in

 

[Hui]

Jack

I have no idea where the DDDDDD etc goes

I thought the results come from B1:B5, B8


But looking at the game sheet that is obviously not the case

I think this will be easy to setup with a Data Table if you:
1. Consider that we know nothing
2. Step us through which input values go where for each iteration
3. Where do the results come from
4. List the iterations

 

[Jack-P-Winner]

Jack

I have no idea where the DDDDDD etc goes

I thought the results come from B1:B5, B8
View attachment 19354

But looking at the game sheet that is obviously not the case

I think this will be easy to setup with a Data Table if you:
1. Consider that we know nothing
2. Step us through which input values go where for each iteration
3. Where do the results come from
4. List the iterations

Hui, The numbers you have circled are what will be produced after running all the combinations on the sheet called Generator. These are the type numbers I want to paste to a new sheet. Narayan pointed out that it actually will have 63 different ways to produce the output that I am looking for. I would focus on A:M and not the other columns

here is a copy of that sheet and all the U and D combinations are in column B

My idea is to have this macro run every combination possible and I will place a link on here to show you how I envision this to work but I prefer not to keep the link up long so I would appreciate it when you have it downloaded if you let me know. My work is very close to me Here is the link Hui. This has all our correspondence and is a bit lengthy. If you have any questions let me know. I even left in the portion about you (-: I do not want to hide or talk about anyone behind their backs. The link also has about 18 game sheet pictures how the UD sheet moves which I believe will simplify any questions

Thanks for wanting to help!!!!

https://app.box.com/s/hhdhz4b5jmvs7sk5wssu7pe6aj2gw7xh

 

[Hui]

Jack, Narayank

Can either of you please explain where the DDDDD etc elements go?
I realise that you want to record all the Combinations of DDDDD to UUUUU and EEEEE to UUUUU

Then what cells need to be monitored as results in each run?

My Suggestion is get rid of all the color
Mark the 5 Input Cells (Yellow) and Output cells (Green)
eg:


I am quite happy to assist here but I am not going to open 17 JPG Files and try and decipher them

 

[Jack-P-Winner]

Just now seeing this, give a little while to redo the sheet and explain

 

[Jack-P-Winner]

I have taken away most of the colors. My only caution is the cells now that are white still have formulas in them which is needed for the OUTPUT cells in columns AI:AN, EVERY row pretty much works the same way when you put a U or D or E or O in the area they need to go which is explained on the sheet. Let me know what questions I can answer in addition to what I have done and again I appreciate your expertise in this!!!! I will be working on a better explanation on how I would like the simulation to work if you need additional input. I have to remember that what is easy to me in understanding my sheets is not necessary easy to anyone else so my apologies.

 

[Jack-P-Winner]

These are all the combinations that Narayan was kind enough to correct for me for this game and any other minus the respective rows as I have explained on the sheet. If you need one for the Es and Os I can do one. I am basically running through all these combinations of Us and Ds with am E and then an O in all respective cells that an E and O go

 

[Jack-P-Winner]

Before I forget. Thank you Hui, you have pushed me into taking another look at my conditional formatting by asking me to change the colors. This is a WONDERFUL thing. THANK YOU!! I mean this ......

 

[Hui]

Jack

You have told me how it works

I need to know if I have a sequence of eg: DUDUDD
Where does it go and why?
What do you want recorded as a result?
What happens next ?

Pretend I know nothing of the game you are trying to model, which I don't

 

[Jack-P-Winner]

In the attached I am using the BB section as an example. What ever you would program for the BB section would be exactly what will be programed in the other sections. The BB section only uses the #1 because it is a stand alone ball that us drawn. All the other sections are for the ball#1 through #4, #5 or #6

The goal is to place every combination in this game sheet that is in the U_D combination sheet with a E and then an O in I:29 J:29, I:32 J:32 and I:35 J:35

so if you were starting out with the first combination on the sheet it is
DDDDDD so you would do this below:

E in I:29 D in U:29 D in AG:29
E in I:32 D in U:32 D in AG:29
E in I:35 D in U:35 D in AG:35

Next:

E in I:29 D in U:29 D in AG:29
O in I:32 D in U:32 D in AG:29
O in I:35 D in U:35 D in AG:35

Next:

E in I:29 D in U:29 D in AG:29
O in I:32 D in U:32 D in AG:29
E in I:35 D in U:35 D in AG:35

Next:

E in I:29 D in U:29 D in AG:29
E in I:32 D in U:32 D in AG:29
O in I:35 D in U:35 D in AG:35

Next:

O in I:29 D in U:29 D in AG:29
O in I:32 D in U:32 D in AG:29
O in I:35 D in U:35 D in AG:35

Next:

O in I:29 D in U:29 D in AG:29
E in I:32 D in U:32 D in AG:29
O in I:35 D in U:35 D in AG:35

Next:

O in I:29 D in U:29 D in AG:29
E in I:32 D in U:32 D in AG:29
E in I:35 D in U:35 D in AG:35

Next:

O in I:29 D in U:29 D in AG:29
O in I:32 D in U:32 D in AG:29
E in I:35 D in U:35 D in AG:35

You would do this with every combination on the sheet labeled U_D_ Combinations and then move on to Ball #1 in the top sections, Ball #2, Ball #3, #4 and #5

Now, as you move through all these different combinations it will start taking away numbers in columns AI:AN, some combinations will leave NO NUMBERS at all and that's fine, that just means that combination does not work, others will leave several and some only 1 number. Whenever it leaves a number or group of numbers I want to record them on a sheet to themselves. You can have all the answers on 1 sheet (#1-#2-#3-#4-#5-#6-BB) however you want to lay them out. I can give you an example on the answer sheet in the next post so you can start reading this one. I hope this all makes sense

Thanks

 

[Jack-P-Winner]

The answer sheet can loo like this here, I basically want to record all the numbers each combination finds

 

[NARAYANK991]

Hi Larry ,

There is one mistake in the uploaded picture file , where T29 is shown as T26. Please confirm.

Secondly , I think we can summarise the Us and Ds as all Us will go in the U columns ( columns T and AE ) while all the Ds will go in the D columns ( columns U and AF ).

For the Es and Os , you show both of them being placed in column I ; is this correct ? Or should the Es go in column I while the Os go in column J ?

Narayan

 

[Jack-P-Winner]

Hi Larry ,

There is one mistake in the uploaded picture file , where T29 is shown as T26. Please confirm.

Secondly , I think we can summarise the Us and Ds as all Us will go in the U columns ( columns T and AE ) while all the Ds will go in the D columns ( columns U and AF ).

For the Es and Os , you show both of them being placed in column I ; is this correct ? Or should the Es go in column I while the Os go in column J ?

Narayan

your correct T29. I was only trying to capture the entire section. My bad for not explaining this

Again you are correct with E's in I and O's in J

I'm not sure what you mean by placing all the U's and D's in their own columns The cell location is critical to their own area(s) Numbers, 50+, OB Hits, BB. If you start moving them around their conditional formatting and Outputs (AI:AM) will not correctly work

Sorry fir my errors and thank you for checking. I knew this one IMHO would be a tough one