Excel macro outputs to sequential lines -- but stops at 64

I have an excel file which makes a regular DB query.
The results are entered into the Q and R column -- and each new query places the results in the next row.

It runs fine -- until row 64 is filled.  ALL subsequent queries place the data back in row Q2 and R2 -- overwriting what is already in them.

I can't see any reason why it won't continue to row 65 and beyond.

Below is the macro code:

Option Explicit
Public dTime As Date

Sub ValueStore()
Dim dTime As Date
    Range("R" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("o2").Value
    Range("Q" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Now
    Call StartTimer
End Sub


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

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


Thanks for any help with this mystery!
graceoutAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayData AnalystCommented:
What formula is in "O2"?

Also, where is the rest of your code?  There's nothing posted that is actually writing query results to your workbook.
0
graceoutAuthor Commented:
Here is the actual file.


Thanks!
0
NorieVBA ExpertCommented:
Change Cells(Rows.Count).Row to Rows.Count.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

graceoutAuthor Commented:
0
Martin LissOlder than dirtCommented:
Does this help?

Sub ValueStore()
Dim dTime As Date
    Range("R" & Range("R1048576").End(xlUp)).Row.Offset(1, 0).Value = Range("o2").Value
    Range("Q" & Range("R1048576").End(xlUp)).Row.Offset(1, 0).Value = Now
    Call StartTimer
End Sub

Open in new window

1
NorieVBA ExpertCommented:
Here's the code with my suggestion implemented.
Sub ValueStore()
Dim dTime As Date
    Range("R" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("o2").Value
    Range("Q" & Rows.Count).End(xlUp).Offset(1, 0).Value = Now
    Call StartTimer
End Sub

Open in new window

1
NorieVBA ExpertCommented:
FWIW Cells(Rows.Count).Row returns 64.
1
graceoutAuthor Commented:
That did it, Norie!

Thanks all!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.