excel_1
New Member
- Joined
- Jul 12, 2021
- Messages
- 5
- Office Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- Platform
- Windows
Have data in columnA and trying to filter data using keywords. member of groups is in the down adjacent cells. starting with {space} or just a cell without space.
The above code is extracting text correctly but only if group is starting without {space}. if the group is multiline. only partial matching can be extracted.
some examples on expected o/p.
Rich (BB code):
tom1.0 jerry mickey mouse
tom jerry2/1
mickey3+4 mouse
tom
jerry
mickey
Mouse
mickey mouse tom jerry2/1
mickey mouse
tom1.0 jerry
tom jerry
tom
jerry
mouse-x mickey
mouse mickey
tom jerry2/1 mickey
VBA Code:
Sub Mymacro()
Range("B2:B2000").Clear
For Each Cell In Sheets(1).Range("A1:A2000")
matchrow = Cell.Row
Find = "*" + Worksheets("Sheet1").Range("B1") + "*"
If Cell.Value Like Find Then
Cell.Offset(0, 1).Value = Cell.Offset(0, 0).Value
End If
Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Call Mymacro
End If
End Sub
The above code is extracting text correctly but only if group is starting without {space}. if the group is multiline. only partial matching can be extracted.
some examples on expected o/p.