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
Sub nexttry()Please Login or Register to view this content.
'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 SubPlease Login or Register to view this content.
Bookmarks