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]

As C3 should contain a captured value from A2 I suggest using a different cell, say G1 and use the formula

=IF(NOW()>TIMEVALUE("14:00:00"),1,0)

and modify the code to

Private Sub Worksheet_Calculate()
If Range("G1").Value = 1 Then Exit Sub
If Cells(Rows.Count, "C").End(xlUp).Value <> Range("A2").Value 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 Sub

 

[Juan C]

I have no way to thank you for all the knowledge you have shared with me.

It works.... Thanks!!!

JC

 

[Juan C]

Hi VoG....

Is there a way to limit the timing in ready the value at A2. Can I read that data every 5 seconds??

Thanks in advance...

JC

I have no way to thank you for all the knowledge you have shared with me.

It works.... Thanks!!!

JC

 

[VoG]

Take a look at the OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[Juan C]

Sorry VoG.... but this goes above my head. I don't know how to combine the macro you gave me yesterday:

Private Sub Worksheet_Calculate()
If Range("G1").Value = 1 Then Exit Sub
If Cells(Rows.Count, "C").End(xlUp).Value <> Range("A2").Value 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 Sub

With the one you are sending me now...???

Thanks

JC

Take a look at the OnTime method http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[VoG]

Remove your existing code.

Right click the Excel logo

just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
End Sub

Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:05"), "MyMacro"
End Sub

Then, whilst still in the code window, Insert > Module and paste in:

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
End Sub

Change Sheet1 to the name of your sheet. Save, close and re-open your workbook.

 

[Juan C]

I'm getting this message:

Public dTime As Date
Sub MyMacro()
dTime = Now + TimeValue("00:00:05")
Application.OnTime dTime, "MyMacro"
With Sheets("Data 14-15")
.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
End Sub

It does not work...

Help!!

Remove your existing code.

Right click the Excel logo

just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
End Sub
 
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:05"), "MyMacro"
End Sub

Then, whilst still in the code window, Insert > Module and paste in:

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
End Sub

Change Sheet1 to the name of your sheet. Save, close and re-open your workbook.

 

[VoG]

What message? An error message?

 

[Juan C]

What message? An error message?

Let me check.... it might be my error.... Give me 5

Thanks

JC

 

[Juan C]

I'm getting this Compile Error: Ambiguous name detected: dTime

Any ideas???

Remove your existing code.


Right click the Excel logo

just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime dTime, "MyMacro", , False
End Sub
 
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:00:05"), "MyMacro"
End Sub

Then, whilst still in the code window, Insert > Module and paste in:

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
End Sub

Change Sheet1 to the name of your sheet. Save, close and re-open your workbook.