VBA: replace part of time string

Question

Column C has time (formatted as text in the form of a sheet that will be exported as csv) in the format HH: mm: ss.

Values ​​C1, C2, C3 refer to the time 09:15:00, 09:16:00, 09:17:00 respectively until 15:29:00

It is NECESSARY TO REPLACE ONLY the last part of ": 00" with ": 59"

--- --- CATCH Column C will display values ​​such as 10:00:00 or 11:00:00 or 12:00:00

This means that directly replacing “: 00” with “: 59” will damage the exact 10 hours, 11 hours, etc.

Column C will be filled with thousands of such data points. My logic below does not work, I think:

{

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 this code is wrong, but all I could think of.

a request for help in executing this logic.

EDIT: There was not enough detailed explanation. Even these full hours should be replaced by such as: 10:00:59, 11:00:59, 12:00:59

Answer 1

If the value does not end at 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 only 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 

Answer 2

You are on the right track. You just need to break the line first, replace in the second half, and then join then. Here is a general purpose function that you can improve with error handling:

 Function ReplaceEnd(s As String, endCount As Integer, replaceWhat As String, replaceWith As String) Dim baseString As String Dim replaceString As String baseString = Left(s, Len(s) - endCount) replaceString = Right(s, endCount) ReplaceEnd = baseString & Replace(replaceString, replaceWhat, replaceWith) End Function 

Edit: usage example:

 secrep = ReplaceEnd(Secsz, 3, ":00", ":59") 

Answer 3

you can use this:

 Public Function AlterTime(rInputRange As Range) Dim oCell As Range For Each oCell In rInputRange.Cells oCell = TimeSerial(Hour(oCell), Minute(oCell), 59) Next oCell End Function 

Answer 4

Use the split function and then analyze and modify.

 Private Sub CommandButton1_Click() Dim ws As Excel.Worksheet Dim lastRow As Long Dim szTimeParts() As String Dim lRow As Long lRow = 1 lastRow = ws.Cells(ws.Rows.count, "C").End(xlUp).Row Set ws = ActiveWorkbook.Sheets("Sheet1") ws.Activate 'Loop through the rows Do While lRow <= lastRow 'Make sure we have a value to read into our string If ws.Range("C" & lRow).Value <> "" Then szTimeParts = Split(Trim(ws.Range("C" & lRow).Value), ":") If szTimeParts(1) <> "00" Then ws.Range("C" & lRow).Value = szTimeParts(0) & ":" & szTimeParts(1) & ":59" End If End If lRow = lRow + 1 Loop End Sub