Need help on taking a data from a cell & keep pasting in subsequent rows in a column?

I want to sample a variable data in a cell say C3 & to display that data after every 1 second (Means sampling rate is 1 Hz) into the rows (one after one) in a column say D. To explain, after starting up the Macro, value of C3 should display in D1 and after 1 second updated value of C3 should display in D2 and so on for 900 times because I want this sampling for 15 minutes (15*60=900 seconds) and want to know average (of course variable) of those 900 cells (filled as well as empty) from D1 to D900.
to achieve this I have used following code

" Option Explicit
Public dTime As Date

Sub ValueStore()
Dim dTime As Date
Range("D" & Cells(Rows.Count).Row).End().Offset(1, 0).Value = Range("C3").Value
Call StartTimer
End Sub


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

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

Two separate Command Buttons are created for StartTimer() & StopTimer().

Now 1st problem is Value starts displaying from Cell D2 but that is not an issue for me. The main issue is above code gives only 63 sampled values means from D2 to D64 & after that no data displays in D65 and subsequent cells but Value of D3 keeps updating. I could not understand why D65 & subsequent cells are not displaying updated C3 value. There seems nothing that put limit at 63 values & why value of D3 keeps updating? Please help me out.

Additional Functionality Required:
Now, I want to display only 900 values after that (15 minutes) all cells from D2 to D64 should be blank and whole process of sampling should start from D2 to D901 so that I can watch variable Average of next one by one 900 values also. Can somebody provide me additional code for this?

I use MS Office 2013.

Sincere thanks in advance.

try this

Please Login or Register  to view this content.

Thank you very much JLGWhiz.
Your code works perfectly.
To achieve additional functionality mentioned in thread I have modified your Sub t & added one more Sub Repeat. Now new code is

" Sub t()

Dim i As Long, s As Variant
For i = 1 To 900
Range("D" & i) = Range("C3").Value
s = Timer + 1
Do While Timer < s
DoEvents
Loop
Next
Range("D1:D900").ClearContents

End Sub


Sub Repeat()
Dim i As Long
For i = 1 To 4
Call t
Next
End Sub "

I am adding some more functionalities like creating a disable button to stop whole process etc.

Once again a sincere thanks to JLGWhiz!

You're welcome, but you need to be putting code tags on the code when you post it. To do that easily, use the mouse pointer to highlight (select) the code, then click the pound symbol (#) in the tool bar. The code tags will be applied to your code and it will retain formatting when you click the 'Post Quick Reply' button.
Regards, JLG