5

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

Vaibhav
  • 123
  • 8

4 Answers4

2

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
Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Thanks Alex. My mistake i was not quite elaborate in explaining. If `Right$(cell.Value, 5) <> "00:00" Then` this part avoids replacing ":00" to ":59" for full hour values like 10:00:00, 11:00:00 etc. Even these full hour values need to be replaces such as: 10:00:59, 11:00:59, 12:00:59 Request you to pls relook – Vaibhav Oct 05 '15 at 14:01
  • Thanks for time and efforts Alex K. This edited one worked perfectly. :) – Vaibhav Oct 05 '15 at 14:39
2

You're on the right track. You just need to split the string first, replace in the second half and then concatenate afterwards. Here's a general-purpose function that you might wanna enhance with some 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: Example usage:

secrep = ReplaceEnd(Secsz, 3, ":00", ":59")
DanL
  • 989
  • 1
  • 8
  • 17
  • not sure what i am doing wrong, but time column C still has all :00 (last 3 characters) un-replaced. Didn't get any error as well. Any clues mate ? – Vaibhav Oct 05 '15 at 14:24
  • Did you insert the replaced string into the cell? E.g. Range("C1:C" & LastRow).Value = secrep – DanL Oct 05 '15 at 14:29
  • Yes, DanL i did that. There wasn't any error just the file with time not changed. I will try the same once more now. – Vaibhav Oct 05 '15 at 14:51
  • i inserted the function in module 2 and placed this in right order within my existing macro `Dim secrep As String` `lastRow = Cells(Rows.Count, "C").End(xlUp).Row` `secrep = ReplaceEnd(Range("C1:C" & lastRow).Select, 3, ":00", ":59")` but it didn't make any change to C column ! – Vaibhav Oct 05 '15 at 15:06
1

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
Bas Verlaat
  • 842
  • 6
  • 8
  • Hi Bas Verlaat, let me try. Just a headsup that i have formatted the column as text and do not want excel to interfere with Time Format. Reason is excel spoils date-time values when exported as csv. So not sure if your code will help. Let me try. – Vaibhav Oct 05 '15 at 14:53
  • Hi. You can put a `Text(Val,"hh:mm")` function around so it will be text again. The benefit of this approach is that it cannot go wrong, because the actual time is reconstructed. – Bas Verlaat Oct 06 '15 at 08:07
0

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
MatthewD
  • 6,719
  • 5
  • 22
  • 41
  • thanks for sharing. I might be missing something, i inserted this code in another macro (as this is a stage in data cleaning process) and the final csv that came out had no change in it. Not sure why. Any way i can explain to you better ? or if i applied code incorrectly? – Vaibhav Oct 05 '15 at 14:11
  • This just makes the changes to the open workbook. You will have to save it manually or put in a save like ActiveWorkbook.Save at the bottom. – MatthewD Oct 05 '15 at 14:19
  • Here is info on saving as csv http://stackoverflow.com/questions/10551353/saving-excel-worksheet-to-csv-files-with-filenameworksheet-name-using-vb – MatthewD Oct 05 '15 at 14:21
  • Thanks Matthew, thing is my sheet operates on data and then saves it as CSV. In that CSV, i couldn't find the changes. – Vaibhav Oct 05 '15 at 14:28
  • thanks for taking out time and efforts for helping. i am going to try it again now. – Vaibhav Oct 05 '15 at 14:40
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/91402/discussion-between-user3531520-and-matthewd). – Vaibhav Oct 05 '15 at 14:47