CODEWITHSUNDEEP
excelvba
Emily
Emily

Reputation: 97

Using Excel Macros to reformat cell contents

I'm trying to use Excel macros to change the formatting of cells. I used Google Translate for a rough translation of some subtitles for a screencast (I'm using .vtt format) but it's mucked up the formatting.

So the format I want is:

00:00:00.000 --> 00:00:01.000

and what it's changed it to is:

00: 00: 00,000 -> 00: 00: 01,000

What I have so far is:

ActiveCell.Select
    Dim String1 As String
    String1 = ActiveCell.Characters
    Replace(String1, " ", "") = String1
    Replace(String1, "->", " --> ") = String1
    Replace(String1, ",", ".") = String1
    ActiveCell.Offset(3, 0).Select

I'm then going to loop through the whole document - so selecting the cell 3 down from the edited one and performing the same operations.

What am I doing wrong that's not working? Thank you.

Upvotes: 2

Views: 65

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Something like this: 

Dim String1 As String, c as range

Set c = ActiveCell
Do While Len(c.Value) > 0
    String1 = Replace(c.Value, ": ", ":")
    String1 = Replace(String1, " -> ", " --> ")
    String1 = Replace(String1, ",", ".")
    c.value = String1
    Set c = c.Offset(3, 0)
Loop

Upvotes: 4

Related Questions