Find and Replace Macro

[whitethomas12]

Does anyone know how to find a value within a text string and replace
the entire cell value with a single word

For Example:

Do While Activecell.Offset(0, -1).Value <> ""
IF Activecell.value <> "(IN)" then
Activecell.value = "IN"
ElseIF Activecell.value <> "(OUT)" Then
Activecell.value = "OUT"
ElseIF Activecell.value <> "INNER" Then
Activecell.value = "INNER DOOR"
Else
Activecell.value = ""
End IF
Loop

What I am trying to do is to find the text value of (IN) , (OUT), and
INNER within a text string in cell.

The above example does not work. I know if I write three loops using
the find and replace method it should work, but this is not very
proficient in an excel worksheet that has more that 2000 rows. I need
to find a way to write one loop for this

Thank you for all of your help in advance

 

[FSt1]

hi
you have a serious problem. the word IN is also within the word INNER.
here is something that may work for you. back up your data BEFORE you run
this.
note that i search for INNER and have code select that option before
searching for IN.
Sub findit()
Dim r As Range
Dim rd As Range
Set r = Cells(2, 2) 'B2...change to suit
Do While Not IsEmpty(r)
Set rd = r.Offset(1, 0)
If r.Value Like "*INNER*" Then
r.Value = "INNER DOOR"
ElseIf r.Value Like "*OUT*" Then
r.Value = "OUT"
ElseIf r.Value Like "*IN*" Then
r.Value = "IN"
Else: r.Value = ""
End If
Set r = rd
Loop
End Sub

worked in my test of 6 lines

Regards
FSt1

 

[whitethomas12]

hi
you have a serious problem. the word IN is also within the word INNER.
here is something that may work for you. back up your data BEFORE you run
this.
note that i search for INNER and have code select that option before
searching for IN.
Sub findit()
Dim r As Range
Dim rd As Range
Set r = Cells(2, 2) 'B2...change to suit
Do While Not IsEmpty(r)
    Set rd = r.Offset(1, 0)
    If r.Value Like "*INNER*" Then
        r.Value = "INNER DOOR"
        ElseIf r.Value Like "*OUT*" Then
            r.Value = "OUT"
            ElseIf r.Value Like "*IN*" Then
                r.Value = "IN"
                Else: r.Value = ""
    End If
    Set r = rd
Loop
End Sub

worked in my test of 6 lines

Regards
FSt1










- Show quoted text -

OK that make since. I changed my code like this:

Range("A2").Select

Do While ActiveCell.Value <> ""
If ActiveCell.Offset(0, 1) Like "*INNER*" Then
ActiveCell.Offset(0, 1).Value = "INNER DOOR"
ElseIf ActiveCell.Offset(0, 1) Like "*(OUT)*" Then
ActiveCell.Offset(0, 1).Value = "OUT"
ElseIf ActiveCell.Offset(0, 1) Like "*(IN)*" Then
ActiveCell.Offset(0, 1).Value = "IN"
Else: ActiveCell.Offset(0, 1).Value = ""
End If
ActiveCell.Offset(1, 0).Select

Loop

It seems to work every time

Thank you for all of your help