1

is there a way to look for specific word in cells even if it's within more text i've been trying below but it only works if the only data exsits within a cell is the word.

Sub Tucana()
 Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, cel As Range
 Dim rngCopy As Range, lastR1 As Long, lastR2 As Long
 Dim strSearch1 As String, strSearch2 As String
 
 strSearch1 = "Tucana"
 strSearch2 = "Tuc"
 Set sh1 = Worksheets("Sheet1")
 Set sh2 = Worksheets("Sheet2")
 lastR1 = sh1.Range("A" & Rows.Count).End(xlUp).Row
 lastR2 = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
 
 Set rng = sh1.Range("A1:A" & lastR1)
 For Each cel In rng.Cells
    If cel.Value = strSearch1 Or cel.Value = strSearch2 Then
        If rngCopy Is Nothing Then
            Set rngCopy = sh1.Rows(cel.Row)
        Else
            Set rngCopy = Union(rngCopy, sh1.Rows(cel.Row))
        End If
    End If
 Next
 If Not rngCopy Is Nothing Then
    rngCopy.Copy Destination:=sh2.Cells(lastR2, 1)
 End If
End Sub
DesTro
  • 9
  • 5

0 Answers0