Loop Macro Needed

[coliervile]

Goooooooooood day to everyone,

I think a loop marco is what I need in this situation. On sheet2 I have data that has RAND and RAND formulas in Columns A and B, other data in column C, and in column D (range D1:D9) I have a formula that returns either the word "True" or "False". I'm not very good with loops.
I want a macro to;
1. When run it calculates the sheet2
2. Look through the range D1:D9 and if there word "True" shows up then
3. recalculate the worksheet 2 until the word "True" does not show up.


Think a macro that a loop macro would be best in this case, but I'm open to other ideas too.

[coliervile]

Here is an image of Sheet2. The columns references are different.
The Columns are A, B, C, and E and F.


0.808

8

Don


Barb

FALSE



0.4897

5

Neal


Lisa

FALSE



0.8589

9

Elizabeth


Ann

FALSE



0.179

1

Barb


William

FALSE



0.2497

3

Ann


Neal

FALSE



0.774

7

Jim


Rich

FALSE



0.3965

4

William


Jim

FALSE



0.6548

6

Rich


Don

FALSE



0.2479

2

Lisa


Elizabeth

FALSE







0.4198
5
Don

William
FALSE


0.8031
6
Neal

Lisa
FALSE


0.9267
9
Elizabeth

Jim
FALSE


0.3836
4
Barb

Barb
TRUE


0.8576
7
Ann

Don
FALSE


0.2238
3
Jim

Neal
FALSE


0.1059
1
William

Ann
FALSE


0.9023
8
Rich

Rich
TRUE


0.1297
2
Lisa

Elizabeth
FALSE

[Bob Phillips]

Where does the data in C & E come from?

[coliervile]

Hello "xld" (one of favorite people here on VBA Express) thank for looking at my question.
Here is the information you requested:
Column A- A1:A9 is =RAND() copied down
Column B- B1:B9 is =RANK(A1,$A$1:$A$9,1) copied down
Column C- C1:C9 is the names
Column E- E1:E9 is =VLOOKUP(ROW(A1)*1,$B$1:$C$9,2,) copied down
Column F- F1:F9 is =E1=C1 copied down to test true/false
There is nothing in column D.
I want all of the names in column E not to be in the same row as they are in column C.
If there is an easier way to do this I'm open to all ideas.
The auto calculate is turned off and set to manual.
When the F9 key is depress the number in column E are changed. Sometimes the number are random and there are no dupes in the same rows in column C and E, column F shows the Trues and Falses.

[Bob Phillips]

I think this is what you want


Public Sub test()
Dim cnt As Long

With ActiveSheet

Do While Application.CountIf(.Range("F1:F9"), "TRUE") > 0

.Calculate
cnt = cnt + 1
Loop

MsgBox cnt & " iterations"
End With
End Sub


I think it can probably done just with formulae, but I will need to look that up, and I will post back if I can get it to work.

[coliervile]

Thanks "xld" for the coding, it does what I want it to do. I would be interested also in seeing a formulae if you're able to find one.

Best regards,
Charlie