• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Excel VB To Copy Text In String and Paste In 2nd Worksheet

Hello,

Can someone assist in developing a VB macro that will do the following:

1.  In Sheet1, column AG, there is a string of text and numbers.  The code looks at the string, and copys the text that appears between:  'Context ' & '@' symbol.  Please note there is a space that appears after 'Context' that should not be included in the copy.  The data being copied on average is 7 characters long.

2.  Paste that data in Sheet2, column A

Thank You!!!
0
Escanaba
Asked:
Escanaba
  • 3
  • 3
1 Solution
 
EscanabaAuthor Commented:
One last note, the code should start in row 2 of column AG on Sheet1 and loop until there is no more data found in that column.  Thanks!
0
 
Patrick MatthewsCommented:
Question: does the string 'Context ' always appears, and is it always followed some number of characters later by an '@' symbol?
0
 
EscanabaAuthor Commented:
Yes.  The word Context_ will appear in every line that has text in it starting in row 2, and will always have characters following it with an @ symbol appearing after the characters.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Patrick MatthewsCommented:
The following seems to work for me:

Sub DoIt()
    
    Dim LastR As Long
    
    With Worksheets("Sheet1")
        LastR = .Cells(.Rows.Count, "ag").End(xlUp).Row
    End With
    
    With Worksheets("Sheet2").Range("a2:a" & LastR)
        .EntireColumn.ClearContents
        .Formula = "=IF(Sheet1!AG2<>"""",MID(Sheet1!AG2," & _
            "SEARCH(""Context "",Sheet1!AG2)+8,SEARCH(""@"",Sheet1!AG2," & _
            "SEARCH(""Context "",Sheet1!AG2))-SEARCH(""Context "",Sheet1!AG2)-8),"""")"
        .Value = .Value
    End With
    
    MsgBox "Done"
    
End Sub

Open in new window

0
 
EscanabaAuthor Commented:
Looks like that did the trick.  Thank you for putting this together!
0
 
Patrick MatthewsCommented:
You're welcome :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now