Using Excel Macros to reformat cell contents

Question

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.

you are not actually resetting the value of the cell. Use `ActiveCell.Value = String1` to do that. But really, you should [avoid ActiveCell\Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and work directly with the objects. Also, looping with a count of 3 will help a lot :) — Scott Holtzman, Oct 15 '18 at 21:07
Yes it is. So I need to remove all the spaces (except for surrounding the " --> " and change the comma to a full stop, for every third cell, starting with the active cell. — Emily, Oct 15 '18 at 21:15

score 4 · Accepted Answer · answered Oct 15 '18 at 21:30

4

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

answered Oct 15 '18 at 21:30

Tim Williams

154,628
8
97
125

1

Perfect, thank you so much. I will pull this apart to learn from it. Much appreciated!! – Emily Oct 15 '18 at 21:35

Using Excel Macros to reformat cell contents

1 Answers1