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

Improve this question

4 Answers 4

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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
Thanks for time and efforts Alex K. This edited one worked perfectly. :)
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")
Improve this answer

4 Comments

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 ?
Did you insert the replaced string into the cell? E.g. Range("C1:C" & LastRow).Value = secrep
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.
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 !
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
Improve this answer

2 Comments

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.
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.
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
Improve this answer

6 Comments

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?
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.
Here is info on saving as csv stackoverflow.com/questions/10551353/…
Thanks Matthew, thing is my sheet operates on data and then saves it as CSV. In that CSV, i couldn't find the changes.
thanks for taking out time and efforts for helping. i am going to try it again now.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.