Hi guys I'm having some difficulty finding how to do the following. I need to have a column add 1 to every cell below the starting cell say `A1`

every second since a time has passed say `13:00:00`

in cell `B1`

. Consider every cell in column `A:A`

is blank I need to either use excel or vba to put a 1 to the cell below , every second, as already mentioned. So after a minute I will have the below up to 60 starting the count from A2

`A 0 1 2 3 . . . ... 60 `

until I specify stop in a cell e.g. `c1`

having `13:01:00`

or `D1`

having `13:01:00`

and `C1`

having `STOP`

I would prefer to do this with excel formulas but I am not sure if it will be efficient since I will have up to nine column doing this at the same time and they will run from a few minutes to a few hours, possibly days. So a UDF would probably be better - I will probably need to test both methods

Once the time has stopped I need all the cells in the column to be put back to blank including the `0`

:)

What I am actually doing is find the lowest low or lowst high frm the point in time when I opened a trade with a program called multicharts refering to its DDE connectoin formulas. The last input is the time since in any period I like as long as it is present in multicharts. I can have 1 second - 5 seconds - 10 minutes. The formulas are like

`=RTD("mcrtdserver.rtdserver","localhost","Symbol","EUR/USD","1 Second","SymbolData","High",0)`

where the 0 is the present 1 second or it can be present 5 minutes. Hence I can use the 1 and 2 to find the data from 2 seconds ago up to however many seconds ago I opened the trade. Essentially I'm looking for the most practical way then to refer to these formula(s) to find the minimum or maximum of the high / low. Low formula is just `=RTD("mcrtdserver.rtdserver","localhost","Symbol","EUR/USD","1 Second","SymbolData","Low",0)`

where it is more practical to work in 5 minutes, 15 minutes, 30 minutes and hours as they will be present in multicharts quite frequently. I am just trying to get my head round how best to do this - whether it would be a vlookup table with time from / time until using some intervals to hone in on the larger time frame or something else. I would then try to paste.value the minimum or maximum value of the high / lows recorded before clearing the column to make use of the value so that bit isn't so important right now.
--------------Solutions-------------

Here's a VBA solution - Note: you cannot do ANYTHING in Excel while this is running. Also, what are you counting in column A for if it just gets cleared out when the timer is done?

Also, since you can't go in and add "STOP" in a cell, while running, I wrote this assuming that in C1 is the Start time (hours, minutes, seconds in 24 hour format), and the stop time in C2.

`Sub timer()`

Dim nowTime As Variant, setTime As Variant, stopCell As String, stopTime As Variant

Dim i As Integer

```
```i = 1

stopCell = Evaluate(Cells(1, 3).Value)

setTime = Format(Cells(1, 2).Value, "HH:mm") ' Get the time to start via B1

stopTime = Format(Cells(2, 2).Value, "HH:mm") 'get the time to stop via B2

nowTime = Format(now(), "HH:mm") 'what time is it right now?

Do While nowTime >= setTime And nowTime <= stopTime

Cells(i, 1).Value = i - 1 'because you also want to start with 0 - if not, remove the "-1"

Application.Wait (now + TimeValue("00:00:01"))

i = i + 1

nowTime = Format(now(), "HH:mm")

Loop

Columns(1).Clear

MsgBox ("Done")

`End Sub`

But I think I'm missing something - so please let me know what that is. And as far as I know, a formula won't be able to do this - you'll need VBA. But again, what's the purpose of counting this sequentially? You wouldn't be able to work in Excel (AFAIK) while a macro is running.