Run a macro when a cell change value to some certain value.

[pavar94]

Hello to everyone.
I am new, but I am usually reading this forum when I need some help with my VBA.

I was running these macros. The first macro copy and paste some values. The second one recall the first macro every minute since you click the button(and will keep going over time). Something like a loop. At the end of the day I will have an historical series minute by minute.
Here is the code:

1° macro
Sub Click()
If Range("AE2").Value = 1 Then
Range("A2:AD2").Copy
Range("AH4").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
Call Test
End Sub


2° macro
Sub Test()
Application.OnTime Now + TimeValue("00:01:00"), "Click"
End Sub


Ok, this is working pretty good. What is the problem? I need to click the button at hours:minute:00 perfectly to have the price at the beginning of every new minute. If I will click at XX:XX:13 I will have all the series with 13 seconds, but I need with seconds=0.

So, I start to think how to fix this little problem. I thought in this way:
1) Create a cell (the cell is AB3) with "=NOW()" that gives me the exact present time.
2) Create another cell (now the cell is AA3) with "=SECOND(AB3)" that gives me the exact second from the hours.
3)Change the second macro (test) to something that will runs the first macro (click) when AA3 = 0.

I looked in many forums and I have found it how run a macro but it gives me a problem (argument not optional). The macro is this:
Sub Test(ByVal Target As Range)
Set Target = Range("AA3")
If Target.Value = 0 Then
Call Click
End If
End Sub


How can I change this macro to have something that runs the first macro when AA3 is equal to 0? Consider that AA3 change the values every second from 0 to 59.
Thank you to everyone will help me.

 

[Micron]

Not sure I follow all of that, but I think you want to alter Application.OnTime start time to
Application.OnTime TimeSerial(Hour(Now), Minute(Now), 0)

TimeSerial(Hour(Now), Minute(Now), 0) should give you a value that is always the current hour and minute at zero seconds. I'm not sure if that is where you need to put it though.

 

[pavar94]

Thank you for your reply. Very kind.

You understood perfectly what the problem was.

I did not think about it... but probably it will work!

I need to wait until financial markets open again and I let you know if we solved this problem.

 

[pavar94]

Not sure I follow all of that, but I think you want to alter Application.OnTime start time to
Application.OnTime TimeSerial(Hour(Now), Minute(Now), 0)

TimeSerial(Hour(Now), Minute(Now), 0) should give you a value that is always the current hour and minute at zero seconds. I'm not sure if that is where you need to put it though.

Ok, I tried in this way but is not working...

I used this:
Sub Test()
Application.OnTime TimeSerial(Hour(Now), Minute(Now), 0), Procedure:="Click", Schedule:=True
End Sub

I do not understand why, but is working, but not how I would like. I try to explain.
Every minute when second=00, run the first macro and copy and paste the values I need.
The problem is that start to go in loop. I would like to have 1 row copied and pasted, but in this way the macro copy and paste something like 20 row in this way:
first raw 10:00:00
second 10:00:01
third 10:00:02
fourth 10:00:03
fifth 10:00:03
.... 10:00:04
..... .......

and when time will be 10:01:00 the macro will do it again...
I think the macro read the time and the time is still seconds=00... But honestly I do not know.

 

[Micron]

Try this as a test
In workbook module:

Private Sub workbook_Open()
SetOnTime
End Sub

In a standard module at the top I have

Option Explicit
Public alertTime As Variant

In that module I have:

Sub SetOnTime()
alertTime = TimeSerial(Hour(Now), Minute(Now) + 1, 0)
Application.OnTime alertTime, "eventMacro"
End Sub

and

Public Sub EventMacro()
MsgBox alertTime
SetOnTime
End Sub

In testing, that raises a message box every minute on the exact minute of the hour.