CODEWITHSUNDEEP
vbaexcel
maximus
maximus

Reputation: 99

Excel VBA: How to find a certain substring

In column B, I have data for e.g. hmc1, hmc2, hmc3. I want to find every Column B cell that contains "hmc" and replace its corresponding cell in Column A with "Found". My code so far works if it matches fully, but not if it matches a substring.

        If .Range("B" & r).Value = "hmc " Then
            .Range("A" & r).Value = "Found" 

Col A   Col B
Accept  hmc1
123     hmc1
123     hmc2
Accept  xcc
Accept  xcc
Accept  xcc
Accept  xcc
Accept  xcc
Accept  xcc
Accept  xcc
Accept  xcc
Accept  xcc
Accept  xcc
123     hmc3
Accept  hmc3
Accept  hmc3

Upvotes: 0

Views: 513

Answers (1)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

Assuming your B column data starts with 2

Sub test()
    Dim lastrow As Long
    lastrow = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To lastrow
        If InStr(LCase(Range("B" & i).Value), "hmc") Then
            Range("A" & i).Value = "Found"
        End If
    Next i
End Sub

enter image description here

Upvotes: 2

Related Questions