Avatar of DrTribos
DrTribosFlag for Australia asked on

macro to scroll through words selecting and replacing text until...

Hi Experts

I'm trying to correct units in a document.

Basically I want to replace:
"meters per second" with "m/s"
"l/minute" with "L/min"

In the first example (m/s) I look at the word after my number (number selected by other code) and if it is in the array I replace it with the correct unit (arrays have corresponding entries: i.e. if element 5 in findUnit is meters then element 5 in repUnit is also 5). So I find meters --> replace with m, find per --> replace with /, find second --> replace with s THEN find the NEXT word and determine that it is not a unit so GoTo FindNextNum.

I'm ok to find the first word after the number but get confused trying to find subsequent words... dealing with "/" if the oringal text was meters/second --> would need to become m/s and dealing with:

 "."
non-breaking space,
paragraph and end-of-cell markers...

An additional requirement is that there are no spaces in the final product.

findUnit and repUnit are Arrays
unitRng is a range

Many thanks, Steve
FindNextUnit:
unitRng.Select
    For i = LBound(findUnit) To UBound(findUnit)
        If Trim(unitRng.Text) = findUnit(i) Then
            MsgBox (findUnit(i) & " will be replaced by " & repUnit(i))
            
            unitRng.Select
            Set nUnitRng = unitRng
            nUnitRng.Next(unit:=wdWord, Count:=1).Select
            Set nUnitRng = Selection.Range
            nUnitRng.Select
            MsgBox ("Next Unit Preselected")
            unitRng.Select
            Selection.TypeText (repUnit(i))
 
Set unitRng = nUnitRng
            unitRng.Select
            MsgBox ("Next Unit")
            GoTo FindNextUnit
        End If
    Next i

Open in new window

Microsoft Word

Avatar of undefined
Last Comment
DrTribos

8/22/2022 - Mon
GrahamSkan

I can't work out why you aren't using Find. Like this:
Sub MultipleReplace(rngArea As Range, findUnit() As String, repUnit() As String)
Dim i As Integer
    For i = LBound(findUnit) To UBound(findUnit)
        With rngArea.Find
            .Text = findUnit(i)
            '.MatchCase = True
            .Replacement.Text = repUnit(i)
            .Execute Replace:=wdReplaceAll
        End With
    Next i
End Sub

Open in new window

ASKER
DrTribos

Hi Graham,

I can (& must) only replace units that directly follow numbers...

he came second in the race <-- second should not be replaced
set flow to 5L/second to ensure <-- second must be replaced

ASKER
DrTribos

The way I'm thinking:

Find the number
Check that it is a number
(####$$$ = ok as $$$ could be units, but $### is not ok)
In not a number --> find next number
Check the units $$$ (select units, use find & replace to scroll through arrays)
Check if the next word is also units (loop until no more units)
Then find next number


trap that I have fallen for:
number ranges:
4 - 6 should be 4-6
4 to 6 should be 4-6


Thanks, Steve
Your help has saved me hundreds of hours of internet surfing.
fblack61
GrahamSkan

OK Steve,
Then I think that you will have to use Wildcards. Here is a useful article:

http://word.mvps.org/FAQs/General/UsingWildcards.htm
Sub MultipleReplace(rngArea As Range, findUnit() As String, repUnit() As String)
Dim i As Integer
    For i = LBound(findUnit) To UBound(findUnit)
        With rngArea.Find
            .Text = "(<[0-9]{1,})" & findUnit(i) 'word must be numeric, followed by required text
            .MatchWildcards = True
            .Replacement.Text = "\1" & repUnit(i) 'replace with text "\1" in first pair of () brackets & new text
            .Execute Replace:=wdReplaceAll
        End With
    Next i
End Sub

Open in new window

ASKER
DrTribos

Reading...  I think you're right

Zen tip: when using wildcard searches: don't wrinkle your brow or bite on your tongue while thinking it through  you have to keep up a regular expression. :-|

I think I've been doing the tongue thing...

Thank you :-)
ASKER
DrTribos

Hi Graham

Sorry I don't understand how to use your code, do I call it from my own Sub?

If so what is the syntax?

Call MultipleReplace (unitRng, unitRng.text, unitRng.text)  ???

Thanks, Steve

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GrahamSkan

Not quite. You can pass the complete arrays and the whole range to be searched.
e.g.
MultipleReplace  MyDoc.Range, findUnit(), repUnit()
ASKER
DrTribos

OK thanks.

I'm on a steep learning curve right now... it might take a while for me to get my head around your suggestions & wild cards and to start implementing and testing the code properly

But I like how short your code is :-)
ASKER
DrTribos

Hi Graham

The following gives the error:

Compile Error

Type mismatch: array or user defined type expected

Thanks

Sub FixUnits()
Dim findUnit As Variant
Dim repUnit As Variant
Dim unitRng As Range
 
findUnit = Array("second", "per", "meters", "amp", "hertz", "hz", "litres", "minute", "mpa", "kpa", "gpl", "minutes", "hours")
repUnit = Array("s ", "/ ", "m ", "A ", "Hz ", "Hz ", "L ", "min ", "MPa ", "kPa ", "g/L ", "min ", "hr ")
 
MultipleReplace Selection.Range, findUnit(), repUnit()
End Sub
 
 
Sub MultipleReplace(rngArea As Range, findUnit() As String, repUnit() As String)
Dim i As Integer
    For i = LBound(findUnit) To UBound(findUnit)
        With rngArea.Find
            .Text = "(<[0-9]{1,})" & findUnit(i) 'word must be numeric, followed by required text
            .MatchWildcards = True
            .Replacement.Text = "\1" & repUnit(i) 'replace with text "\1" in first pair of () brackets & new text
            .Execute Replace:=wdReplaceAll
        End With
    Next i
End Sub

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
GrahamSkan

The code was expecting a string array.
If you are using an array which is a varian, the expected parameters would be.

Sub FixUnits()
Dim findUnit As Variant
Dim repUnit As Variant
Dim unitRng As Range
 
findUnit = Array("second", "per", "meters", "amp", "hertz", "hz", "litres", "minute", "mpa", "kpa", "gpl", "minutes", "hours")
repUnit = Array("s ", "/ ", "m ", "A ", "Hz ", "Hz ", "L ", "min ", "MPa ", "kPa ", "g/L ", "min ", "hr ")
 
MultipleReplace Selection.Range, findUnit(), repUnit()
End Sub
 
 
Sub MultipleReplace(rngArea As Range, findUnit As Variant, repUnit as Variant)
Dim i As Integer
    For i = LBound(findUnit) To UBound(findUnit)
        With rngArea.Find
            .Text = "(<[0-9]{1,})" & findUnit(i) 'word must be numeric, followed by required text
            .MatchWildcards = True
            .Replacement.Text = "\1" & repUnit(i) 'replace with text "\1" in first pair of () brackets & new text
            .Execute Replace:=wdReplaceAll
        End With
    Next i
End Sub
GrahamSkan

Oops. I seem to have had the wrong text in my clipboard, so that doesn't read quite right.
ASKER CERTIFIED SOLUTION
GrahamSkan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
DrTribos

now I have "subscript out of range" for both findUnit() and repUnit
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
GrahamSkan

I guess that you are using the uncorrected version and hadn't seen my correction.
ASKER
DrTribos

You guessed correct.

Code is working now - now I need to get my head around the wild cards, although I'm not sure that I can achieve what I want with the wild cards.

My problem is that I am unable to predict all the permutations that I might encounter:

for example m/s could be written as:

* meters per second
* metres per second
* meters / second
* meters per sec
* mps

I really like your find replace code - what I need and was most stuck on was the code to find the next "word" and pass it back to find replace.

Rationale is find a number, text after number is likely to be units, if I can find that text in my list then express as units in preferred way. So using the above example that takes care of meters.

A typical string might be:

Set speed to 55 meters per second to meet production requirement.

Now I have to look at "per", is per in my list, yes - replace with /, then shift focus to second, then shift focus to the next word. I also need to look at "to" but should discover that "to" is not in my list having found a unit that is not in the list I assume that all units are taken care of (yes - we will be reviewing these) and then look for the next word.

I'm using selection.next unit:=wdword to find the next possible unit... it's getting messy. Will revisit in the morning - late here.

Cheers & thanks for your help

Steve
ASKER
DrTribos

Still having trouble selecting subsequent words and checking if they are valid units.

The wild card is great to get a number followed by a unit...

But I need to check the subsequent words (i.e. the words after the unit) to see if they could also be units... things get complicated when I try to accommodate "." and "/" characters... otherwise it *seems* almost straight forward

looking at tags, also reason why I want to scroll through words (scroll, as stated in title) is so that user has opportunity to confirm each replacement.

any further help / suggestions greatly appreciated.

Thanks, Steve
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
DrTribos

Ahhh that's clever! I'll be able to test in a few hours - thank you

My recent brain attack (still untested) was going to be a bunch of sequential if statements... anyway will let you know how it goes. Cheers
ASKER
DrTribos

Hi Graham

Your latest contribution to my macro was the most successful to date - thank you. While the macro runs substantially as desired there is a problem whereby a word that is not in the units list becomes modified because the first letter (or first n letters) match a string in my array.

e.g. I need to have "g" in my find array because it stands for grams... original text might have been g/litre and I need to correct to g/L - code only loops while the last potential unit found was a valid unit.

what happens:

I might have a phrase like "3m/second gives optimum..." and the macro converts m, /, and second and then finds the "g" (in gives) and changes it with "g" from my array. Then (as required) all spaces are removed using:

replace(myTrim, " ", "") where myTrim is the range from the start of the number found to the end of the last unit changed.

I guess this issue could be addressed by saying unitRng must be a whole word - then I need to handle space versus no space between number & word

I'm using a tag for "/" : at start of macro find "/" replace with " /xx "  can do same for "%",

worst case I may have to shorten the array and put up with some extra manual corrections.

Ideas, suggestions, comments welcome,

Kind regards,
Steve

NextUnit:
        For i = LBound(findUnit) To UBound(findUnit)
        unitRng.End = unitRng.Start + Len(findUnit(i))
        unitRng.Select
       ' MsgBox (unitRng)
        If Trim(unitRng.Text) = Trim(findUnit(i)) Then
            unitRng.Select
            ync = MsgBox(findUnit(i) & " will be replaced by " & repUnit(i), vbYesNoCancel)
            If ync = vbYes Then
                unitRng.Text = repUnit(i)
                Selection.Expand unit:=wdWord
                Selection.Collapse direction:=wdCollapseEnd
                Set unitRng = Selection.Range
                GoTo NextUnit  'Exit For
            Else
            If ync = vbCancel Then GoTo myEnd
            End If
            
        End If
        Next i

Open in new window

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
DrTribos

Hi Graham - sorry for the long delay all our contracts in Australia were cancelled just after my last post. I've had a lot of tidying up to do and now they are sending me to the UK (I think). Bloody economic meltdown!! Anyway thanks for your help. I learnt a lot from this topic so thanks for that too :-)

Kind regards, Steve
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.