CODEWITHSUNDEEP
excelvba
Aakash Amar
Aakash Amar

Reputation: 11

Check if cell contains a specific substring

I want to loop through the cells in a column and check if each cell contain a specific substring.

I have the code for the exact search but I don't know how to search for a substring

Sub Macro1()
    For i = 2 To 2500
        If Cells(i, 7).Value = "ALSO DEUTSCHLAND GMBHLMP" Then
            Cells(i, 2).Value = "sometext"
        End If
    Next i
End Sub

A substring search for LMP should also give the above output.

Upvotes: 0

Views: 1296

Answers (2)

BigBen
BigBen

Reputation: 50008

Use InStr to test if a String contains a sub-string.

If InStr(1, Cells(i, 7).Value, "LMP", vbBinaryCompare) > 0 Then

You have several options for the string comparison (last argument):

vbUseCompareOption: Performs a comparison by using the setting of the Option Compare statement.

vbBinaryCompare: Performs a binary comparison.

vbTextCompare: Performs a textual comparison.

I have proposed vbBinaryCompare assuming you want this to be case-sensitive.

NOTE: You can use Like as well, as demonstrated in this question.

Upvotes: 4

Storax
Storax

Reputation: 12167

I'd probably use the find method of a range like that

Sub FindIt()

    Dim rg As Range
    Set rg = Range("G2:G2500")

    Dim fndRg As Range
    Dim firstAddress As String

    With rg

        Set fndRg = .Find("ALSO DEUTSCHLAND GMBHLMP", , xlValues)

        If Not fndRg Is Nothing Then
            firstAddress = fndRg.Address
            Do
                fndRg.Offset(, -5).Value = "some text"
                Set fndRg = .FindNext(fndRg)
            Loop While Not fndRg Is Nothing And fndRg.Address <> firstAddress
        End If
    End With

End Sub

Upvotes: 2

Related Questions