CODEWITHSUNDEEP
excelvba
Anirudh Chauhan
Anirudh Chauhan

Reputation: 111

Searching for substring in the main string through VBA

I have a code below with the help of which I am trying to achieve pattern matching.

The snapshot shown below is the data from Sheet2.I have ShortDescription which is the main string and the corresponding number in Inc No

enter image description here

Another snapshot is from Sheet3. I have Keyword which is the substring that I want to find in the ShortDescription from Sheet2 and Output is the desired output which is the all the corresponding Inc No against values found in the main string.

enter image description here

So far, I have written the below code:

Option Explicit

Private Sub CommandButton1_Click()
Dim str2, Cell, intVal As Variant
str2 = ThisWorkbook.Sheets("Sheet3").Range("A2").Value
For Each Cell In ThisWorkbook.Sheets("Sheet2").Range("A2:A4")
    intVal = Cell.Value
    If intVal Like "*" & str2 & "*" Then
        MsgBox "Got it"
    End If
    Next Cell   
End Sub

So for example, Tushar is 2 times in main string. MsgBox is coming 2 times. This is just there to check that my code is working so far. I dont know how to proceed.

Upvotes: 0

Views: 73

Answers (1)

SJR
SJR

Reputation: 23081

This works for me. Assign it to your button. However, it might be better to turn it into a custom function and then you can apply to multiple cells.

Sub x()

Dim str2 As Variant, Cell As Range, s As String

With ThisWorkbook
    str2 = .Sheets("Sheet3").Range("A2").Value
    For Each Cell In .Sheets("Sheet2").Range("A2:A4")
        If Cell.Value Like "*" & str2 & "*" Then
            s = s & vbLf & Cell.Offset(, 1).Value   'add col B number and line feed to the string
        End If
    Next Cell
    .Sheets("Sheet3").Range("B2").Value = Mid(s, 2) 'return final string missing initial line feed
End With

End Sub

Upvotes: 1

Related Questions