VBA - Search text and Replace string in another cell

[gd6noob]

Hi there,

I tried searching but could not find the answer, perhaps Im not using the correct search terms.

Im trying to search in 1 column and if its finds string or part of a string, then it would replace another cells values, in the same row.

Example,
Under Job column, C3 and C6 both contains 234, then I want B3 and B6 to be changed to Yes.
The number of rows will vary.

Book1
	A	B	C
1	Name	Place	Job
2	test	zzz	123
3	test	zzz	234
4	test	zzz	345
5	test	zzz	123
6	test	zzz	234
7	test	zzz	345
Sheet1

 

[johnnyL]

Copy this formula down the B column
= IF(C2=234,"Yes","zzz")

 

[gd6noob]

Copy this formula down the B column
= IF(C2=234,"Yes","zzz")

Hi thanks for the reply.

I should have been clear, the Name and Place columns I used were just fillers.. they would be actual names and places.

 

[johnnyL]

Is vba code an option?

 

[gd6noob]

Is vba code an option?

Sorry for the late reply, Yes, VBA is definitely a better option

 

[johnnyL]

How about:

Sub MatchReplace()
    Dim ArrayValue      As Long
    Dim LastRow         As Long
    Dim Rng             As Range
    Dim FirstAddress    As String
    Dim MyArr           As Variant
'
    MyArr = Array("234")                                                        ' Strings to search for in the C column
'
    With Sheets("Sheet1")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
'
    With Sheets("Sheet1").Range("C2:C" & LastRow)
        For ArrayValue = LBound(MyArr) To UBound(MyArr)
            Set Rng = .Find(What:=MyArr(ArrayValue), After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            If Not Rng Is Nothing Then                                          ' If match found then
                FirstAddress = Rng.Address
'
                Do
                    Rng.Offset(0, -1).Value = "Yes"                             '   Place our string in the other cell
                    Set Rng = .FindNext(Rng)
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            End If
        Next ArrayValue
    End With
End Sub

 

[gd6noob]

Thank you so much.. this works well.

 

[johnnyL]

Thank you so much.. this works well.

Thank you for the quick response.

Just an FYI, you can add more strings to test for in the line:

    MyArr = Array("234")                                                        ' Strings to search for in the C column

For example, if you wanted to check for "234" & "345", you could edit that line of code to:

    MyArr = Array("234", "345")                                                       ' Strings to search for in the C column

In that case, all of the "234"'s would be marked "Yes" and all of the "345"'s would also be marked "Yes"

@gd6noob Thank you for reporting back your status!