A Timer question

[SpHawk]

I have a sheet in which numbers keep being added to a cell until it reaches a set number. Say 1, every 3 minutes until it reaches 7.

Alternately the time may need to be set to 5 minutes or 7 minutes. And the number it totals at may change.

I read the timers that were put up, and none of them seem to be adding numbers to cells, so I thought I'd ask for help.

I was going to set the first variable in one cell, and then in another cell use an if statement to start the timer when the number dropped below the first until it returned to the right total, that being the amount of time needed.

Is there a way of doing a while / wend in excel? I could start the time manually, but wondered what could be done.

Thanks in advance,
Bob

 

[RolfJ]

I am not sure whether I correctly understood all your requirements, but this code might get you started in the right direction, assuming that you have the parameters for the conditions you would like to work with in the cells specified in the constant declarations at the top of the code:

Const CELL_COUNTER = "C22"
Const CELL_MAX_AMOUNT = "C23"
Const CELL_INCREMENT = "C24"
Const CELL_CHECK_TIME = "C25"
 
Sub IncrementCounter()
    Dim currentTime As Date
    Dim counter As Long
    counter = Range(CELL_COUNTER).Value
    Dim maxAmount As Long
    maxAmount = Range(CELL_MAX_AMOUNT).Value
    Dim increment As Long
    increment = Range(CELL_INCREMENT).Value
    Dim checkTime As Double
    checkTime = Range(CELL_CHECK_TIME).Value
    currentTime = Now()
    While counter < maxAmount
        DoEvents
        If Now() >= currentTime + TimeValue("00:" & checkTime & ":00") Then
            counter = counter + 1
            Range(CELL_COUNTER) = counter
            currentTime = currentTime + TimeValue("00:" & checkTime & ":00")
        End If
    Wend
End Sub

Hope this helped,
Rolf Jaeger
SoarentComputing
http://soarentcomputing.com

Phone: 800.580.0068
Cell: 510.300.7462

 

[SpHawk]

I'm sorry I wasn't clear, but thank you for the help.

I have 5 kids. Math program. There will be tasks to do when the number drops. And a timer that will give you time to do it. 3min for hard, 4 min for medium and 5 for easy. Everytime you get a problem right, the number drops again, giving you more time to complete the sheet.

I was just trying to get a timer to add 1 pt to a cell until it reached it's max. (The number of problems x2) It would be great if the timer started on its' own, but really isn't important. Just a sheet I started writing to keep my younger kids interested, and maybe challenge the 2 oldest. Going to make a game of it, and all that.

Thank you for your help,
Bob

 

[VoG]

I'm not clear exactly what you want to do but take a look here - it may give you some ideas http://www.box.net/shared/gjudf86qh9

This is a multiplication test with a maximum time for completion. Take a look at the timer code.

 

[ChristineJ]

I looked at the timer at http://www.box.net/shared/gjudf86qh9
that VoG recommended on this thread. This is exactly what I need except rather than seconds I need hours and minutes. Thanks.