Loop until...

[lui_roc]

I would like some help on VB code that will check a cell for its value
and loop a macro until either the value is meet or a time limit is up.
In total there are three criteria's the cell value can fall in.
for example:

' the first part of the text below is code that works the rest of it
I've written using comments

Do
Sort
Loop Until Range("K36").Value = between - 1 And 1


' or until timer = 5secs

'if loop times out (e.g. timer = 5 secs) then loop Loop Until
Range("K36").Value = between - 2 And 2

or until timer = 5secs

'if times out then loop Loop Until Range("K36").Value = between - 3 And 3
My thanks in advance

[Ken Puls]

Hi there,

I'll leave you to finish the -3 to 3 part, but this should get you started:

Dim StartTime As Long

'Record start time
StartTime = Timer

'Loop until value correct or 5 seconds have passed
Do
If Range("K36").Value > -1 And Range("K36").Value < 1 Then
Exit Do
End If
Loop Until Timer - StartTime > 5

HTH,

[lui_roc]

Thanks for that it works fine, however I'm having difficulty taken that step further. I have pasted the code I have attempted to give you a clearer idea of what I'm trying to do.


StartTime = Timer
'Loop until value correct or 5 seconds have passed
Do
Sort
If Range("K30").Value > -1 And Range("K30").Value < 1 Then
Exit Do
End If
Loop Until Timer - StartTime > 5
'if the value is not between -1 and 1 it loops again until next value criteria
'is correct or 5 'seconds has passed
If Range("k36").Value > 1 And Range("K36").Value < -1 Then
Do
Sort
If Range("K36").Value > -2 And Range("K36").Value < 2 Then
Exit Do
End If
Loop Until Timer - StartTime > 5
End If


'Basically cell k36 automatically generates numbers. If the number appear in the first given range (between -1 and 1) the number generates stops. If the 5 seconds has passes the next range is tried (between -2 and 2) for a further 5 seconds. This is repeated once again for the range -3 and 3

[Bob Phillips]

You need to do a couple more things

-restart the Timer
-exit the sub when matched

and what does sort do?

[Ken Puls]

By the way, Lui_roc, I've edited your posts to use our VBA tags. They make your code a lot more readable.

:)

EDIT: <sigh> The tags seem to be splitting themselves for some reason. I'll get that looked into.

[mdmackillop]

Hi Ken
I found some CodeGlue and joined them together

[Ken Puls]

Good stuff, thanks, Malcolm.

It looks like my code glue doesn't work in Firefox, for some reason...

[lui_roc]

thanks guys - it works great!