Problem changing text in Word using wildcards

[steve61]

The aim of my macro is to detect the first instance of numbers to 2 decimal places in a Word doc using wildcards, and then to paste a number copied from an Excel spreadsheet to replace that number.

The original number can be positive or negative and I am struggling with this as it changes the number of characters. My workaround was to assume the original number was negative and then search to see whether the resulting text after I had pasted the new number needed an additional space: the occasions where this was true were 1) "f-" and 2) "f" follwed by any number from 0 to 9.

For 1) the macro works fine. However for 2) the code finds the text "f[0-9]" using a wildcard, but when I try to replace the text, adding a space, the wildcard is not recognised and it just copies the actual characters, i.e. "f [0-9]"

This is what I have so far - be grateful for any help

Please Login or Register  to view this content.

Sub nexttry()
'Copy new number from excel spreadsheet
Worksheets("5945").Range("A1").Offset(1, 5).Copy

Set appWD = CreateObject("Word.Application.14")
appWD.Documents.Open "P:\Steve\the rest\automation\word automation\Jan 14 template.doc"
appWD.Visible = True
Dim wdDoc As Word.Document
Set wdDoc = appWD.ActiveDocument
Dim wdSln As Selection
Set wdSln = appWD.Selection

'Find first number to 2 dec places in Word doc and select space before in case it is negative
Application.ScreenUpdating = False
With wdSln.Find
.MatchWildcards = True
.Text = "?[0-9]{1,}.[0-9]{2}"
End With

'Paste new number
If wdSln.Find.Execute Then
wdSln.PasteSpecial Placement:=wdInLine, DataType:=wdPasteText
End If

'Check if space needed by highlighting new number and preceding word
wdSln.MoveLeft Unit:=wdCharacter, Count:=6, Extend:=wdExtend

'Check for "f-"
With wdSln.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "f-"
.Replacement.Text = "f -"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With
wdSln.Find.Execute Replace:=wdReplaceAll

'Check for "f" follwed by any number from 0 to 9
With wdSln.Find
.Text = "f[0-9]"
.Replacement.Text = "f [0-9]"
End With
wdSln.Find.Execute Replace:=wdReplaceAll

End Sub

Please Login or Register  to view this content.

[macropod]

You could use a wildcard Find with:
.Text = "<[\-0-9,]{@}.[0-9]{2}>"
That should be enough find any positive or negative number with 2 decimal places. Your replacement text is whatever the Excel range holds.

So:

Please Login or Register  to view this content.

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab.