Link to home
Start Free TrialLog in
Avatar of graceout
graceoutFlag for United States of America

asked on

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!
Avatar of Ray
Ray
Flag of United States of America image

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.
Avatar of graceout

ASKER

Here is the actual file.


Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of Norie
Norie

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

FWIW Cells(Rows.Count).Row returns 64.
That did it, Norie!

Thanks all!