DateDiff VBA function not working

[drmingle]

The below code gives a run-time error 1004 (undefined object) and it is isolating the DateDiff function as the problem. What gives?

Sub Speed_DoesNotWork
Dim start_time, end_time
start_time = Now()
end_time = Now()
Sheets("SpeedLog").Select
Range("C1").End(xlDown).Offset(1, 0).Value = DateDiff("s", start_time, end_time)
End Sub

The below code works fine:

Sub Speed_Works
Dim start_time, end_time
start_time = Now()
end_time = Now()
MsgBox "This CVTY Speed Search was completed in" & " " & (DateDiff("s", start_time, end_time)) & " " & "seconds."
End Sub

 

[RoryA]

Is there anything in C2? If not, you are probably trying to enter the value in the cell below the last cell in column C...

 

[drmingle]

C2 is an empty cell.

What do I need to do fix the problem?

My goal is to fill the next empty cell (row) in Column C each time I run the routine.

"C1" is the header for the column.

Thanks for the help.

 

[RoryA]

Search up rather than down:

Sub Speed_Test()
Dim start_time, end_time
start_time = Now()
end_time = Now()
Sheets("SpeedLog").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = DateDiff("s", start_time, end_time)
End Sub

 

[drmingle]

Works great...

Thanks.

 

[drmingle]

Yikes...

Any idea why the value is being replaced each time the search is being done?

C1 = Header
C2 = Value
**Each time I run the test, I would like a new value placed below the filled cell**

Test 1 = C2
Test 2 = C3
Test 3 = C4, etc

 

[drmingle]

Would this code help at all in getting the successive values to be inserted in the empty cell below?:

Sub InsertCellValues()
Dim LastRow As Integer        ' input below to specify how many rows to check beginning with 1st row
Dim counter As Integer        ' loop counter from 1 to lastrow
Dim InvertedCounter As Integer        ' counter from lastrow down to 1
Dim DeleteCondition As Boolean
With Sheets("SpeedLog")
  LastRow = 100
  For InvertedCounter = LastRow To 1 Step -1
    If .Cells(InvertedCounter, 2) = 0 And .Cells(InvertedCounter, 5) = 0 Then
      .Rows(InvertedCounter).Delete
    End If
  Next InvertedCounter
End With
End Sub

 

[drmingle]

That solves with xldown...

Thanks for getting me thinking...