timer problem excel 2007

[jetpack]

I am using the following code to copy changes to a cell value every 30 seconds. The code works, sort of, but has the following bugs;

When timevalue = 5 seconds it copies every 5 seconds but copies the change into 5 cells at a time. It will only fill 64 rows and then stops copying.

If I change the value to 30 seconds, it only copies to 1 row at a time but still will not copy more than 64 rows.

In each case the bottom of the screen still shows copying message at the time interval even though no copying is taking place.

Any help will be greatly appreciated.

Option Explicit
Public dTime As Date

Sub ValueStore()
Dim dTime As Date
    Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A1").Value
    Call StartTimer
    If Range("A1") > 275 Then Beep

End Sub


Sub StartTimer()
    dTime = Now + TimeValue("00:00:30")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub

 

[Ruddles]

Tested here: I couldn't get it to write to five cells at a time but I agree it stops at row 64.

Change the assigment statement to:-

    Range("B" & Cells([COLOR=red][B]Rows.Count, 2[/B][/COLOR]).End(xlUp).Row).Offset(1, 0).Value = Range("A1").Value

I can't think what value Cells(Rows.Count).Row might return.

 

[Andrew Poulsom]

Cells(Rows.Count).Row returns 64. When used without a column index the Cells property goes across then down, ie 1048576/16384 = 64. Use:

Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A1").

 

[Ruddles]

But it works up to (or should that be "down to") row 64.
</P>

 

[Andrew Poulsom]

Once it reaches 64 it continually updates B2 (assuming there's a heading in B1).

 

[jetpack]

Thank you both for your replies.

I appreciate the explanation why it was stopping at row 64.

It is now continually copying, but still multiple rows, now on all time frames.

Question; Since the smallest timevalue interval is 1 second, is excel copying all the changes made during that 1 second? If so, is there any way to copy only the first change?