Find Text add value

Jeff P · Aug 13th 2016

Hello

Im in need of a vba macro which will find text within a column and indicate which row contains this text.

As an example: in A1 a key word is typed in ( which will vary), and in column A14:A500 I have a list of paragraph text information in each row.

The macro will search column A14:A500 for the name that's typed in A1.. if it finds it, offsets an "X" , 3 columns to the right... if it doesn't find it.. it does nothing.

Any ideas how to create this?

Many Thanks!

sktneer · Aug 14th 2016

Re: Find Text add value

This will do the trick....

Code

Sub FindTextAndPlaceValue()
Dim rng As Range
Dim str As String
Application.ScreenUpdating = False
Set rng = Range("A14:A500")
str = Range("A1").Value
If str = "" Then Exit Sub
For i = 1 To rng.Rows.Count
   If rng.Cells(i).Value Like "*" & str & "*" Then
      rng.Cells(i).Offset(0, 3) = "X"
   End If
Next i
Application.ScreenUpdating = True
End Sub

Display More

Jeff P · Aug 14th 2016

Re: Find Text add value

Wow, this works perfectly!

I appreciate the time you put into writing this.
Thank you very, very much, sktneer!!

Cheers!

Jeff

sktneer · Aug 14th 2016

apo · Aug 16th 2016

Re: Find Text add value

This might also do the trick..?

Code

Sub Test()
  [D14:D500] = [If(A14:A500=A1,"X","")]
End Sub

Find Text add value

Participate now!

Share