Loop through the search

[Razor_Rob]

Hi

I've got a code that searches for a certain string in a workbook, then paste it on another workbook. Its working its currently copying a certain string in a Cell in Column 5, Row 2. Then pasting in in a cell.

What I would like to do is search through say Column E2 all the way down till there's string in the column. Then paste all the found string in the destination workbook Column BL2 down. Thanks in advance.

Column E2 Column BL2
something sakjhfjh - WORDINEED akdsjjhfd WORDINEED
something sakjhfjh - 2ndWORDINEED akdsjjhfd 2ndWORDINEED
something sakjhfjh - 3rdWORDINEED akdsjjhfd 3rdWORDINEED

Dim result_string As String
    Dim main_text As String
    Dim search_text As String
    
    
    Dim LastRowAASN As Long
    Dim i7 As Long
    
    LastRowAASN = Range("BL" & Rows.Count).End(xlUp).Row
    For i7 = 2 To LastRowAASN
    
    'to find the position of the hyper space
    main_text = wsCopy.Cells(2, 5).Value
    search_text = "- "
    pos_first = InStr(1, main_text, search_text)
    
    main_text = wsCopy.Cells(2, 5).Value
    search_text = " Portal"
    pos_second = InStr(pos_first + 1, main_text, search_text)
    
    result_string = Mid(main_text, pos_first + 1, pos_second - pos_first - 1)
    wsDest.Cells(2, 64).Value = result_string
        
    Next i7

 

[Razor_Rob]

[TR]
[TD]

E (Column from E2 all the way down till theres data)​

[/TD]

[TD]

BL (Column from BL2 all the way down till theres data)

[/TD]
[/TR]
[TR]
[TD]something sakjhfjh - WORDINEED akdsjjhfd[/TD]

[TD]WORDINEED[/TD]
[/TR]
[TR]
[TD]something sakjhfjh - 2ndWORDINEED akdsjjhfd [/TD]

[TD]2ndWORDINEED[/TD]
[/TR]​