Running and stopping a macro based on a cell value

[c0087]

Hi. I'm using =rand( for the data and setting it to 3 decimal place. As you know anytime you click, input, etc. in any cell, the =rand( automatically changes.

I want a macro, where I can continuously have it input something (i.e. in column D it just keep inputting "1 Enter") so it keeps changing my
data set until it reaches a certain number - let's say 0.001

based on the result in row 4, I want the macro to keep inputting 1 until one of those 3 values reaches STOP (indicating .001)

Any help/solution is much appreciated



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.552[/TD]
[TD]0.252[/TD]
[TD]0.134[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=if(a2=.001,stop,go)[/TD]
[TD]=if(b2=.001,stop,go)[/TD]
[TD]if(c2=.001,stop,go)[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[igold]

Does this do what you want..

Sub test()


    Do Until Range("A2").Value = 0.001 Or _
            Range("B2").Value = 0.001 Or _
            Range("C2").Value = 0.001
        Range("D2").Value = 1
    Loop
    
End Sub

 

[mikerickson]

Instead of writing to a cell, you could trigger the recalc of RAND with the Calculate command

Do Until Range("A2").Value = 0.001 Or _
            Range("B2").Value = 0.001 Or _
            Range("C2").Value = 0.001

       Calculate
Loop

 

[c0087]

I'm getting a compile error : invalid outside procedure on both tries.

I'm still new to code/macros, but all i did was copy/paste both your suggestions and got the same error

 

[igold]

Are you putting in a module in the Visual Basic Editor, and did you paste the entire code...

Also you may want to try to type it in. at the end of the first two lines is a blank and an underscore then hit enter and continue.

 

[c0087]

It was calculating but not stopping at first, so i changed from rand( to randbetween using whole numbers and it works perfectly.

Thank you sir

 

[igold]

We were happy help. Thanks for the feedback.