Offset getting stuck in VBA code

[Latvis]

Hi,

I cannot figure out why Offset in VBA code gets stuck after running few times...

The section of the code below is supposed to record a timer value from C2 in column G. First it checks if the first record exists in F3:G3. There's no value there yet, so it works: records the timer result after pressing the command button 3 (to reset the timer) and resets timer to 0. After resetting the timer for the second time it recognizes the existing values in F3:G3 and records new values in F4:G4. However, after resetting it for the third time and further, it does not record new values in F5:G5 and further, but overwrites the existing values in F4:G4

Private Sub CommandButton3_Click()

If Range("F3") = "" Then
Range("F3").Value = 1
Range("F3").Offset(0, 1).Value = Range("C2").Value
Else:
Range("F2").End(xlDown).Offset(1, 0).Value = Range("F2").End(xlDown).Value + 1
Range("F2").Offset(0, 1).End(xlDown).Offset(1, 0).Value = Range("C2").Value
End If
Range("C2").Value = Format(0, "00") & ":" & Format(0, "00") & ":" & Format(0, "00") & "." & Format(0, "00")
LastTime = 0
ResetIt = True
End Sub

 

[jasonb75]

Try changing F2 to F3 in the Else section. Because F2 is empty the xlDown command stops at the first non blank instead of the last.

It is for reasons like this that it is preferable to start from the bottom and work up rather than from the top down.
You shouldn't have any problems here once you make the suggested change, but in general an intermediate blank would still cause it to fail.

 

[Latvis]

Try changing F2 to F3 in the Else section. Because F2 is empty the xlDown command stops at the first non blank instead of the last.

It is for reasons like this that it is preferable to start from the bottom and work up rather than from the top down.
You shouldn't have any problems here once you make the suggested change, but in general an intermediate blank would still cause it to fail.

Thanks Jason!

changing F2 to F3 did not work, but changing the direction solved the issue. I updated the code in the Else section to

Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Value = Cells(Rows.Count, 6).End(xlUp).Value + 1
Cells(Rows.Count, 6).End(xlUp).Offset(0, 1).Value = Range("C2").Value

and it works fine now. Thanks again