CODEWITHSUNDEEP
vbaexcel
user1649932
user1649932

Reputation: 11

VBA Code to Search Text

I want to amend the code below

I want the findwhat to pick text from a range ("a1:20")-which contains the list of text i want to look for

If it finds the text in the description in B, i want it to return the text it found e.g jo IN range c beside its description

Thanks 

Option Explicit

Sub x()
    Dim FindWhat, rngCell As Range, i As Integer

    FindWhat = Array("Jo", "oa", "of", "koo")
    For i = 0 To 3
        For Each rngCell In Range("B2", Range("B" & Rows.Count).End(xlUp))
            If InStr(rngCell, FindWhat(i)) <> 0 Then
                rngCell.Offset(0, 1) = rngCell
                rngCell.Offset(, 1).Resize(, 2).Copy
            End If
        Next rngCell
    Next i

End Sub

Upvotes: 0

Views: 9392

Answers (1)

mkingston
mkingston

Reputation: 2718

Try this:

Option Explicit

Sub x()
    Dim findRng As Range, _
        targetRng As Range, _
        findCell As Range, _
        found As Range
    Dim i As Integer
    Dim firstFound As String, _
        columnName As String

    columnName = "B"
    Set findRng = Range("A1:A20")
    For Each findCell In findRng
        Set targetRng = Range(columnName & "2", Range(columnName & Rows.Count).End(xlUp))
        With targetRng
            Set found = .Find(findCell.Value, LookIn:=xlValues, lookat:=xlPart)
            If Not found Is Nothing Then
                firstFound = found.Address
                Do
                    found.Offset(0, 1).Value = found.Offset(0, 1).Value & findCell.Value & ", "
                    Set found = .FindNext(found)
                Loop While Not found Is Nothing And found.Address <> firstFound
            End If
        End With
    Next findCell

End Sub

Note that unless what you're doing is a little more complex than this, you can achieve this result using cell formulae. Read the help on the Index, Match and VLookup functions to see how to achieve this.

Upvotes: 1

Related Questions