Single calculation

[Juan C]

Help me here...
Is there a way for me to have a formula perform its calculation one time only... meaning that if the precedent data changes it (the formula) won't compute again, thus leaving the previous number it calculated unchange...

 

[VoG]

If you pasted in the code into multiple modules or more than once in the same module you could get that error.

I've tested the code and it works fine for me.

The only tweak I would suggest is

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dTime, "MyMacro", , False
On Error GoTo 0
End Sub

 

[Juan C]

Dear VoG

OK... thanks! I got it to work, but remember that yesterday we set it up that each sheet will execute between certain hours only. We added that "if" formula to it for that to happen, using G3 as "1" for not to execute and "0" for it to execute. Now with this last correction, that "if" has been disabled.... Any ideas???

Thanks

If you pasted in the code into multiple modules or more than once in the same module you could get that error.

I've tested the code and it works fine for me.

The only tweak I would suggest is

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dTime, "MyMacro", , False
On Error GoTo 0
End Sub

 

[VoG]

Try

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("Sheet1")
    .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
    .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End With
If Now - Date > TimeValue("14:00:00") Then Call StopIt
End Sub

Sub StopIt()
On Error Resume Next
Application.OnTime dTime, "MyMacro", , False
On Error GoTo 0
MsgBox "Timed macro stopped at " & Format(Now, "hh:mm:ss"), vbInformation
End Sub

 

[Juan C]

I have 24 sheets. Each one works within its determined hour. So I think that the time value have to be > 14:00:00 and < 15:00:00

Also can I create as many sub modules as sheets, changing ("Sheet1") to each sheets name?

Thanks

JC

Try

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("Sheet1")
    .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
    .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
End With
If Now - Date > TimeValue("14:00:00") Then Call StopIt
End Sub
 
Sub StopIt()
On Error Resume Next
Application.OnTime dTime, "MyMacro", , False
On Error GoTo 0
MsgBox "Timed macro stopped at " & Format(Now, "hh:mm:ss"), vbInformation
End Sub

 

[VoG]

I wouldn't have 24 timers running - that sounds like a recipe for confusion at best.

Try adapting this:

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("Sheet1")
    If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("15:00:00") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("Sheet2")
    If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("16:00:00") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
'
'put the code for the other 22 sheets here
'
End Sub

 

[Juan C]

OK... great!! This will take me some time to test. I will revert to you once I have tested this formula.

Once again, I have no way to thank you for your collaboration on this matter.

Best regards,

JC

I wouldn't have 24 timers running - that sounds like a recipe for confusion at best.

Try adapting this:

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("Sheet1")
    If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("15:00:00") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("Sheet2")
    If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("16:00:00") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
'
'put the code for the other 22 sheets here
'
End Sub

 

[Juan C]

Dear VoD:

Should I paste the last instruction you sent to me as a "Insert > Module" or in the first page?

Thanks

JC

I wouldn't have 24 timers running - that sounds like a recipe for confusion at best.

Try adapting this:

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("Sheet1")
    If Now - Date >= TimeValue("14:00:00") And Now - Date <= TimeValue("15:00:00") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
With Sheets("Sheet2")
    If Now - Date >= TimeValue("15:00:00") And Now - Date <= TimeValue("16:00:00") Then
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = .Range("A1").Value
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = .Range("A2").Value
    End If
End With
'
'put the code for the other 22 sheets here
'
End Sub

 

[VoG]

No, you should replace the existing code with the new code, then expand it by adding the code for the other 22 sheets where indicated.

 

[Juan C]

Dear VoG:

I did it but it does not work. Nothing seems to happen!!??

Any ideas?

Thanks

JC

Dear VoD:

Should I paste the last instruction you sent to me as a "Insert > Module" or in the first page?

Thanks

JC

 

[VoG]

I have no idea - it works for me. Did you save the workbook then re-open it?