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.

Nick's picture

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

Nick's picture

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.