Find/Add?Replace Macro. Help Please

[setragni]

Hi everyone,
I work with movie script translations, and often we receive scripts that have wrong time codes.
I am working on a macro that searches for any set of two numbers after a ":" and replace the number for that number + 4.
Example.
I need a macro that would take this:
03:04 bla bla bla bla
04:23 bla bla bla bla

and change to this (+4)
03:08 bla bla bla bla
04:27 bla bla bla bla

I have very little experience, this is what I have so far.


Sub fixtimes()
Const S_FIND As String = ":"
Dim Numbers As Integer
Numbers = Val(S_FIND)
Numbers = Numbers + 4
Do While InStr(ActiveDocument.Content, S_FIND) > 0
With ActiveDocument.Content.Find
.ClearFormatting
.Text = S_FIND
.Execute Replace:=wdReplaceOne, ReplaceWith:="" & Numbers & "", _
Forward:=True

I am not sure if this is correct and I don't know how to end it.
Any input is greatly appreciated,
Thanks
-Tony

[gmaxey]

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = ":[0-9]{2}"
.MatchWildcards = True
While .Execute
oRng.MoveStart wdCharacter, 1
oRng.Text = Val(oRng) + 4
oRng.Collapse wdCollapseEnd
Wend
End With
End Sub

[setragni]

Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = ":[0-9]{2}"
.MatchWildcards = True
While .Execute
oRng.MoveStart wdCharacter, 1
oRng.Text = Val(oRng) + 4
oRng.Collapse wdCollapseEnd
Wend
End With
End Sub



:bow:Thank you Gmaxey! You saved us a lot of time...
I am seeing two problems tho. It is not a big deal but maybe it is a quick fix...
1- Results less or equal to 09 are showing as 0,1,2,3,4,5,6,7,8,9. Instead of 00,01,02,03,04,05,06,07,08,09.

2- And I would imagine this one is a bit more complicated so don't bother trying, I don't want to take much of your time.

Minutes are going over 59.

01:58 is changing to 01:62

I guess a good solution would be adding something like "If text > or = to 60, then -60, +4. The other problem would be adding +1 on the minute side to get 02:02.

Thanks again for your help man!:friends:

[gmaxey]

There is probably a more elegant solution, but this should work:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = ":[0-9]{2}"
.MatchWildcards = True
While .Execute
oRng.MoveStart wdCharacter, 1
oRng.Text = Format(Val(oRng) + 4, "00")
If Val(oRng.Text) >= 60 Then
oRng.Characters(1) = "0"
oRng.Characters.First.Previous.Previous = oRng.Characters.First.Previous.Previous + 1
End If
oRng.Collapse wdCollapseEnd
Wend
End With
End Sub

[gmaxey]

Try:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Set oRng = ActiveDocument.Range
With oRng.Find
.Text = "[0-9]{2}:[0-9]{2}"
.MatchWildcards = True
While .Execute
oRng.Text = Format(DateAdd("n", 4, oRng.Text), "HH:MM")
oRng.Collapse wdCollapseEnd
Wend
End With
End Sub

[setragni]

Thank you again Greg! Perfect!

I just had to add >= 60, so I don't get 00:60.

[gmaxey]

I edited the cruder method to take care of the "60." I think the later method posted would be better.

[snb]

another one:


oRng.Text = Format(CDate(oRng.Text) + 1 / 360, "hh:mm")

[gmaxey]

snb is illustrating the fact that a date in Word is just a number where zero "0" represents December, 30 1899 00:00. 1 represents December 31, 1899 00:00, 1.5 represents December 31, 1899 12:00 and so on.

Your 03:04 appearing in a Word document as "time" converts to the date December, 30 1899 03:04. Four minutes represents 1/360 of a day. So if you add 1/360 to the existing date you will add four minutes. The following may help to illustrate:


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim oRng As Word.Range
Dim oDate As Date
Set oRng = ActiveDocument.Range
oRng.Text = "00:00"
'Covert text to date.
oDate = CDate(oRng.Text)
'Note the 0 index date in Word is 12/30/1899 00:00 AM
MsgBox Format(oDate, "mmmm, dd, yyyy - hh:mm")
MsgBox CDbl(oDate) '0 index
'If you add 1, you will add 1 day
oDate = oDate + 1
MsgBox CDbl(oDate)
MsgBox Format(oDate, "mmmm, dd, yyyy - hh:mm")
'If you add 1/24, you will add one hour.
oDate = oDate + 1 / 24
MsgBox CDbl(oDate)
MsgBox Format(oDate, "mmmm, dd, yyyy - hh:mm")
'If you add 1/360, you will add four minutes.
oDate = oDate + 1 / 360
MsgBox CDbl(oDate)
MsgBox Format(oDate, "hh:mm")
'Similiarily if you add .0027778, you will add four minutes.
oDate = oDate + 0.0027778
MsgBox CDbl(oDate)
MsgBox Format(oDate, "hh:mm")
End Sub