CODEWITHSUNDEEP
excelvbacell
Dullspark
Dullspark

Reputation: 215

Excel - VBA - Search for a specific value within a cell

Is it possible to search for a specific value in a column?

I want to be able to search all of the cells in column "B" and look for the 'word' "pip" in it (without being case sensitive). I've got everything else, just need to know if this is possible or how it can be done.

My Current code looks as follows:

Sub A()

    ActiveSheet.Name = "Data"

    Dim ws As Worksheet
    Set ws = Sheets("Data")

    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Sheets.Add(After:= _
            ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws1.Name = "pip"

    ws.Activate
    Row = 2

    Dim i As Integer
    For i = 1 To 10
        If (Cells(i, 2).Value = (HAS pip IN IT) Then 'This is the part that i'm struggling with

            Copied = ws.Range(Cells(i, 1), Cells(i, 17)).Value 'If possible, this would cut and paste so it deleted the original
            ws1.Activate
            ws1.Range(Cells(Row, 1), Cells(Row, 17)).Value = Copied

        Row = Row + 1

        ws.Activate

        End If

    Next i
End Sub

Edit: Just to clarify, the value in column B will never just be "pip". It will be a full sentence but if it contains "pip" then i would like the IF function to work.

Upvotes: 1

Views: 2189

Answers (1)

CallumDA
CallumDA

Reputation: 12113

Find and FindNext work nicely (and quickly!)

    '...
    Dim copyRange As Range
    Dim firstAddress As String

    Set copyRange = ws.Range("B1:B1500").Find("pip", , , xlPart)

    If Not copyRange Is Nothing Then
        firstAddress = copyRange.Address
        Do
            ws2.Range(Cells(Row, 1), Cells(Row, 17)).Value = Intersect(copyRange.EntireRow, ws.Columns("A:Q")).Value
            Row = Row + 1
            Set copyRange = Range("B1:B10").FindNext(copyRange)

        Loop While copyRange.Address <> firstAddress
    End If
    '...

Upvotes: 1

Related Questions