How to add 1 to the cell below every second or period of time

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.


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")

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.

Category:excel Time:2017-12-05 Views:1

Related post

Copyright (C), All Rights Reserved.

processed in 1.072 (s). 13 q(s)