# Thread: Automating single iterations to test random data

1. ## Automating single iterations to test random data

I'm looking for a way to automate the following:

1. I have a 2-dimensional range of cells filled with random numbers.
2. I have a complex bunch of formulas that crunch those numbers to create some new data in other cells.
3. Currently I press F9 repeatedly to get a new set of random data and
4. I stop pressing F9 when a certain calculation cell has a certain value.

A super simple example:

A named range "MYDATA" contains cells \$A\$1:\$H\$8

1. Set every cell in the range MYDATA to contain =RANDBETWEEN(1,6)
2. Formulas as follows:
1. H11 = COUNTIF(MYDATA,1)
2. H12 = COUNTIF(MYDATA,2)
3. H13 = COUNTIF(MYDATA,3)
4. H14 = COUNTIF(MYDATA,4)
5. H15 = COUNTIF(MYDATA,5)
6. H16 = COUNTIF(MYDATA,6)
7. H21 = SUM(H11,H13,H15) //* sum of count of all odd values
8. H22 = SUM(H12,H14,H16) //* sum of count of all even values
9. H24 = SUM(H11:H13) //* sum of count of values 1..3
10. H25 = SUM(H14:H16) //* sum of count of values 4..5
11. G20 = AND(H21=H22,H24=H25) //* success criteria as a boolean value

3. Press F9 repeatedly
4. ... until G20 = TRUE

As you can imagine it takes a long time to get a "successful" set of data (and this is a simplified example). Is there anyway to automate this process?

If it requires VBA to simulate "pressing F9" and to test the value of G20 that is fine, but I do not want VBA to do any of the other formulas. If this can be done without any VBA that would be really great.

Bryan

PS: My system is Win 8.1 using Excel 2010 64-bit.

2. Bryan,

Welcome to the Lounge as a new poster!

Google: "excel goal seeking" w/o quotes. I think this is what you are after.

HTH

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

BryanA (2015-05-29)

4. Thanks RetiredGeek but I'm afraid I already tried Goal Seeking without much success.

Goal Seeking seems to be designed to systematically change the value of just 1 cell to create a direct derivative result.

In my case I am randomly changing the values of 64 cells and calculating aggregate statistical results.

If you could show me how to make Goal Seeking change multiple random numbers I would love to learn.

5. Bryan,

Ok give this code a try:

Code:
```Option Explicit

Sub Test()

Do

Calculate

Loop Until [\$G\$20].Value = True

End Sub  'Test```
Test File: Bryan Testloop.xlsm

HTH

6. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

BryanA (2015-05-29)

7. That did it! Awesome, thank you RetiredGeek!

It immediately worked on my simplified example and it is now working on my complex project 4,000 iterations and counting (I added an iteration counter to your code to measure progress).

Code:
```Sub Test()

[\$X\$2].Value = 0  ' Reset iteration counter cell

Do

Calculate

[\$X\$2].Value = [\$X\$2].Value + 1

Loop Until ([\$G\$20].Value = TRUE Or [\$X\$2].Value = 99999)  'Limit to 100K iterations

End Sub  'Test```
UPDATE: Found my first good dataset in only 8,630 iterations (this was for the complex spreadsheet, not the simple one). Soooo glad I did not have to do that by hand. Thank you again!

8. Bryan,

Glad it worked out for you.

9. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

BryanA (2015-05-29)

10. Hi Bryan

8000+ writes to the sheet. Blimey!
..writing the iteration count to the sheet will slow it down a great deal.

You could instead, show the iteration count in the bottom statusbar, and then, if required, save the final count to the sheet.

zeddy

#### Posting Permissions

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