Recalculate (F9) until condition met

[mihaipir]

Hello,
I would like to calculate some cells until text "ok" appears. I have cells from B41:U43 and i want to recalculate until one of them appears with text "ok" (dosen't matter which one) and if possible tell me how many times had counted until one of them is with text "ok" The formula is already made, but it is annoying to manually refresh and count at the same time.
I have little to no knowledge with VBA

Thank you very much!

[guru008]

Hi,

There is no need to refresh the formula or sheet to update the output of the same. Formula will auto calculate on change of value in reference field

I am able to respond in real time to you until it is solve!

[rcm]

Dear mihaipir: It can be done, but there are some loose ends...

1. What exactly makes "ok" appear?
2. Is data being inputted in the calculation range?
The reasoning behind the question is that the recalculation can be triggered if any cell changes its value within the range.

[mihaipir]

At every "ok" there 7 random numbers, and because of the RANDBETWEEN formula, they sometime repeat and the "ok" appears when those 7 numbers don't repeat.
For example when B1 to B5 is 11223, A1 is blank, and when 1 2 3 4 5, cell A1 it's "ok

This is the formula at the other end =INDEX(Sheet2!$C$2:$C$63,RANDBETWEEN(1,COUNTA(Sheet2!$C$2:$C$63)),1)
and the formula for when not repeating , for "ok" is =IF(AND(W$36<>W$40,W$36<>W$37,W$37<>W$38,W$38<>W$39,W$39<>W$40,W$40<>W$41,W$36<>W$38,W$38<>W$40,W$41<>W$36,W$37<>W$40,W$37<>W$41,W$36<>"",W$37<>"",W$38<>"",W$39<>"",W$40<>"",W$41<>"",W$42<>"",W$42<>W$41,W$42<>W$40,W41<>W$39,W$42<>W$38,W$42<>W$37,W$42<>W$36),W36,"")

Sorry if is primitive, but gives "ok" and that is all that i need. The second formula it's "W" and not sheet2 because i've copied them to my working sheet.

[Trebor76]

Hi mihaipir,

If you cross-post please provide a link to each of the other site(s) as I have done with this link.

Robert

[FDibbins]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

http://www.mrexcel.com/forum/excel-q...ition-met.html

[FDibbins]

oops closed the thread by mistake. It is open again now, apologies

[mihaipir]

Thank you for the info, i didn't know i could not post same post on 2 forums.
I will only post here.

What i am doing, is pressing f9 to and counting in my head, how many times do i press f9 until "ok" appears. "ok" appears when those 5 numbers on the right column are different from eachother. Because those 5 numbers are calculated with RANDBETWEEN function from a range of 1-43 to 1-69 and sometimes they repeat (1 2 2 3 4), and ok is when (1 2 3 4 5) for example. I wanted to know if is anyway using VBA to make them auto recalculate until one of the cells is "ok" and then stop and if possibly tell me how many times had F9 been pressed until "ok" appears.

Thank you!