Macro to search a string, insert a new row, copy existent data, copy matching string

[sn9w]

Hello,

I need to create a macro who can search for a partial string (ex: TAR-WKS or TAR_LCD) on the H,J,I and K columns and if a string with that name it's found it should:

1. Insert a new line below the line where the string it's found.
2. Copy the content of the A,B,C,D and E columns from the line where the search string was found and paste them on the new line inserted at point 1, using same location - A on A, B on B, .., E on E.
3. CUT the found string along with the rest of the text (ex: TAR-WKS152 or TAR_LCD668) to the new line created as follows:

if it's TAR-WKS"a number" it should be pasted on the new line, column F.
if it's TAR_LCD"a number" it should be pasted on the new line, column G.


test environment excel file attached.
test_file.xlsx

[djbomaha]

Hi,

I was unable to open the spreadsheet you attached, but I think I was able to make a sample one and write a macro to do what you were looking for based on the description. The code to do what you listed is shown below. I have attached the *.xls file that includes the macro. Press "<CTRL> a" to run the macro on the data shown on Sheet1. Hit "<CTRL> z" to reset the data on Sheet1.

Hope this helps,

Daniel

Sub FindString()

Dim LastRow As Long
Dim i As Long
Dim j As Long
Dim WKScol As String
Dim LCDcol As String

Sheets(1).Select

WKScol = "F"
LCDcol = "G"

' Set LastRow
Selection.SpecialCells(xlCellTypeLastCell).Select
LastRow = ActiveCell.Row

' find "TAR-WKS" or "TAR_LCD", add row and transfer data

For i = LastRow To 1 Step -1

' < For "TAR-WKS" >
   If (InStr(Range("H" & i), "TAR-WKS") > 0) Then
      j = i + 1
      Rows(j).Select
      Selection.Insert Shift:=xlDown
      Range(WKScol & j) = Range("H" & i)
      GoTo 10
   ElseIf (InStr(Range("I" & i), "TAR-WKS") > 0) Then
      j = i + 1
      Rows(j).Select
      Selection.Insert Shift:=xlDown
      Range(WKScol & j) = Range("I" & i)
      GoTo 10
    ElseIf (InStr(Range("J" & i), "TAR-WKS") > 0) Then
      j = i + 1
      Rows(j).Select
      Selection.Insert Shift:=xlDown
      Range(WKScol & j) = Range("J" & i)
      GoTo 10
   ElseIf (InStr(Range("K" & i), "TAR-WKS") > 0) Then
      j = i + 1
      Rows(j).Select
      Selection.Insert Shift:=xlDown
      Range(WKScol & j) = Range("K" & i)
      GoTo 10
      
' < For "TAR_LCD" >
   ElseIf (InStr(Range("H" & i), "TAR_LCD") > 0) Then
      j = i + 1
      Rows(j).Select
      Selection.Insert Shift:=xlDown
      Range(LCDcol & j) = Range("H" & i)
      GoTo 10
   ElseIf (InStr(Range("I" & i), "TAR_LCD") > 0) Then
      j = i + 1
      Rows(j).Select
      Selection.Insert Shift:=xlDown
      Range(LCDcol & j) = Range("I" & i)
      GoTo 10
    ElseIf (InStr(Range("J" & i), "TAR_LCD") > 0) Then
      j = i + 1
      Rows(j).Select
      Selection.Insert Shift:=xlDown
      Range(LCDcol & j) = Range("J" & i)
      GoTo 10
   ElseIf (InStr(Range("K" & i), "TAR_LCD") > 0) Then
      j = i + 1
      Rows(j).Select
      Selection.Insert Shift:=xlDown
      Range(LCDcol & j) = Range("K" & i)
      GoTo 10
   Else
      GoTo 20
   End If
      
' Copy Columns "A" to "E"
10 Range("A" & i & ":E" & i).Copy
   Range("A" & j).Select
   ActiveSheet.Paste
   Application.CutCopyMode = False

20 Next i

Range("A1").Select

End Sub

[sn9w]

Excel file to work with office 2007 attached. Sheet1 it's the raw data. Sheet2 should be where results are displayed. Sheet 3 it's a sheet to see how sheet2 should look like after running the macro.
test_file(2007).xls

I have 2 thing that i want to add over the 3 from my initial request, for the ones who can help:

Add 4 more partial strings to search for on point 3, second bullet: "if it's TAR_LCD"a number" it should be pasted on the new line, column G." I will need also for macro to search AD_LCD"a number",PR-LCD"a number",BB_LCD"a number",DSI_LCD"a number".
If possible, can the macro post the new lines + initial lines(columns A to E) on 2nd sheet (sheet2)?

@Daniel,
Thanks for the macro but on my side seams to work only for 1st searched(TAR-WKS)item from columns H,I,J,K. The rest(where are more than 1 on line) of TAR-WKS and TAR_LCD isn't working.
I've attached a file with my test data and your macro - 2test_file.xlsm

Valentin.