Excel VBA: How to find a certain substring

Question

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

InStr ?

Vincent G
– Vincent G

2016-05-20 14:06:19 +00:00
Commented May 20, 2016 at 14:06 — Vincent G
– Vincent G, Commented May 20, 2016 at 14:06

Karthick Gunasekaran · Accepted Answer · 2016-05-20 14:14:17Z

2

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

answered May 20, 2016 at 14:14

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

maximus Over a year ago

this is it! Does LCase mean Lower case? How about if Col B, may contain example Hmc, HMC, hmc or 110 Hmc, which i dont care as long as it contains the keyword hmc. will it work? tq

Karthick Gunasekaran Over a year ago

it will work for HMC1 or Hmc1 or hMc1 etc., it will converts to lcase and check with lowercase hmc

maximus Over a year ago

i tried it works perfectly. thanks a lot, you are really expert!

ale10ander Over a year ago

If an answer helped you, don't forget to mark it as correct by clicking the check mark next to that answer.

Collectives™ on Stack Overflow

Excel VBA: How to find a certain substring

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related