Reputation: 111
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
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.
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
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