Need som help with VBA that copy the rpice every sec
HI!
I need som help resolving a VBA problem.
I can't get it to do the task every sec as i whant to it does it one and then stops.
Dim TimeToRun
Sub auto_open()
Call ScheduleCopyPriceOver
End Sub
Sub ScheduleCopyPriceOver()
TimeToRun = Now + TimeValue("00:00:01")
Application.OnTime TimeToRun, "CopyPriceOver"
End Sub
Sub CopyPriceOver()
Set mycell = Cells(65000, 3).End(xlUp).Offset(1, 0)
mycell.Value = Now
mycell.Offset(0, 1).Value = Range("D7").Value
Call ScheduleCopyPriceOver
End Sub
Sub auto_close()
On Error Resume Next
Application.OnTime TimeToRun, "CopyPriceOver", , False
End Sub
In the longer term, I would like it to start the loop 09:00 and stops at 17:30. But did not get it.
It would be nice if you
It would be nice if you included the source of that amazingly written code..
; - >
HI Nick :)
I tried to put together two of your coder but can not get it to work. Why can't I get the loop to work properly.
http://excelexperts.com/VBA-Tips-Run-Code-Every-Hour-Minute-or-Second
http://excelexperts.com/VBA-Tips-Find-The-Last-Cell
ok, so this is the bit that
ok, so this is the bit that sets the time that it runs:
TimeToRun = Now + TimeValue("00:00:01")
- runs every second
TimeToRun = Now + TimeValue("00:01:00")
- runs every minute
TimeToRun = Now + TimeValue("01:00:00")
- runs every hour
etc...
to stop it from running outside hours,
Sub CopyPriceOver()
'####################
if now-date > 4.5/24 then ' only run if the time > 4:30 AM
'####################
Set mycell = Cells(65000, 3).End(xlUp).Offset(1, 0)
mycell.Value = Now
mycell.Offset(0, 1).Value = Range("D7").Value
'####################
endif
'####################
Call ScheduleCopyPriceOver
End Sub
Thx Nick!
Tank You for the help!
I will start with the time problem soon.
The thing is that the code as it was, do not lopp. I get it to copy the text, but only ones and i get a text that tells me that there is somthing wrong with the namne "CopypriceOver".
I would like to fix it before I add the additional code.
Have you any idea what it could be due to?
It's working
Now it is working with looping!
Now will i se if i can get the time to work with the code.