How do I do a find operation until it doesn't find it anymore 1

[bgreenhouse]

Hi There

I'm essentially coding a find and replace method, but I don't want to use the replace function...Let me explain. I want to find phrases that fit a certain wildcard description, select them, do something to them and then put the resulting string back where they were. This works great with the find function (it leaves me with a selection of the found word that I can then access through the Selection object)...I then just manipulate the string and replace the original string with the new string (str_select) with:

Selection.TypeText Text:=str_select

Now, if I could have done this with "replace all", it would just loop through the whole thing until it didn't find any more. I want to do that too, but can't think of a loop condition...

Anyone?

B

 

[kphu]

Bgreenhouse,

Try this code out. I didn't know what you were searching for so I pretended that you were looking for the letter a.

Sub findreplace()
Dim i As Integer
Dim findreplace As Range
Set findreplace = Intersect(ActiveSheet.UsedRange, Range("A1:A65536&quot)
For i = 1 To findreplace.Cells.Count
If findreplace.Cells(i).Value Like "*a*" Or findreplace.Cells(i).Value Like "a*" Then
findreplace.Cells(i).Select
ActiveCell.Formula = "Str_select"
Else
End If
Next i
End Sub

Hope this is what your looking for. Let me know if you need an explanation of the code.

Good Luck

 

[bgreenhouse]

Hi kphu

Sorry, I should have been more explicit...I'm working in Word, not Excel.

Here's the actual code I have written (that works, but I need to loop through it)...

Sub unformat(opentag, closetag)

Dim len_ot As Integer
Dim len_ct As Integer
Dim len_select As Integer

len_ot = Len(opentag) + 1
len_ct = Len(closetag) + 1
searchstring = "/" & opentag & "*" & "/" & closetag
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = searchstring
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
    End With
    Selection.Find.Execute
    Dim str_select As String
    
    str_select = CStr(Selection)
    
    str_select = Right(str_select, Len(str_select) - len_ot)
    str_select = Left(str_select, Len(str_select) - len_ct)
        
    Selection.TypeText Text:=str_select
End Sub

Thanks,

Ben

 

[kphu]

Ben,

I just started using VB about 3 weeks ago and so far I've learned a ton.

Unfortunately, I have yet to have the need to use vb in word.

Good luck with an answer.

 

[JustinEzequiel]

[tt]' Warning! Untested code follows!
Sub unformat(opentag, closetag)

Dim len_ot As Integer
Dim len_ct As Integer
Dim len_select As Integer
Dim str_select As String

len_ot = Len(opentag) + 1
len_ct = Len(closetag) + 1
searchstring = "/" & opentag & "*" & "/" & closetag
Selection.Find.ClearFormatting
With Selection.Find
.Text = searchstring
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
End With

do while Selection.Find.Execute

str_select = CStr(Selection)

str_select = Right(str_select, Len(str_select) - len_ot)
str_select = Left(str_select, Len(str_select) - len_ct)

Selection.TypeText Text:=str_select

selection.collapse wdcollapseend

loop
End Sub
[/tt]

 

[bgreenhouse]

Thanks Justin

That seems to work for me.

Want to explain what you did?

Ben

 

[JustinEzequiel]

I'll try.

The Execute method returns True if Word was able to find a match for your search string or False if not.

The Do While...Loop repeats whatever is within so long as the Execute method returns True.

I did not bother to check whatever it is you do to the found string. Thus, I changed your '.Wrap = wdFindContinue' to '.Wrap = wdFindstop' to prevent an infinite loop in case whatever it is you are doing to the matched text will result in a string that would match the wildcard pattern you are using.

wdFindContinue means that if Word reaches the end of your document (.Forward = True) without finding a match for your search string, Word would go to the start of your document and try searching from there.

Since I put wdfindstop, I decided I needed to put 'selection.collapse wdcollapseend' after whatever it is you are doing to the found string as whatever it is might be leaving a block of text selected. wdfindstop with a block of text selected would tell Word to only search the selection and not the rest of the document which I am guessing is not what you want.

Thus, the code I posted would only work if the insertion point is at the start of the document or at least before any possible match.

Hope this clears thing up and that I did not forget anything.

 

[xlbo]

Ben - that not worth a star? - certainly is from me


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde