VBA: Replace Part of Time String

Question

Column C has Time (formatted as text as sheet will be exported as csv) in format HH:mm:ss.

C1, C2, C3 values are of Time 09:15:00, 09:16:00, 09:17:00 respectively till 15:29:00

Need to REPLACE ONLY the last ":00" part with ":59"

---CATCH--- In column C there will be values such as 10:00:00 or 11:00:00 or 12:00:00

This means a direct replace ":00" with ":59" would corrupt the values of exact 10'o clock , 11'o clock etc..

Column C will be filled with thousands of such data points. My logic below will not work i guess:

{

Dim secrep As String
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
Secsz = Range("C1:C" & LastRow).Select
seczero = Right(Secsz, 2)
secrep = Replace(Secsz, ":00", ":59")

}

i know the above code is wrong but that's all i could come up with.

request for help complete this logic..

EDIT: Was not quite elaborate in explaining. Even these full hour values need to be replaces such as: 10:00:59, 11:00:59, 12:00:59

Answer 1

If the value does not end with 00:00 then update it to :59

Dim cell As Range

For Each cell In Range("C1", Range("C1").End(xlDown))
    If Right$(cell.Value, 5) <> "00:00" Then
        cell.Value = Left$(cell.Value, 6) & "59"
    End If
Next

Edit, to replace just the last 00:

Dim cell As Range

For Each cell In Range("C1", Range("C1").End(xlDown))
    cell.Value = Left$(cell.Value, 6) & "59"
Next