Find and Replace Macro
 

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

Find and Replace Macro
 

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

" wrote:

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

Find and Replace Macro
 

On Jan 8, 12:13*pm, FSt1 wrote:
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



" wrote:
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- Hide quoted text -

- 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