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

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

Select allOpen in new window

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

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

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

Select allOpen in new window

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 :-)

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

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

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 :-)

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

Select allOpen in new window

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

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

now I have "subscript out of range" for both findUnit() and repUnit

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

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

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

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

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

Select allOpen in new window

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