Find Copy Paste

[eddiegb1]

Hi,

I've been searching for a code to handle the following, but can't seem to find the right one.

That is:

Find the "ERROR" string in column A of Sheet1,
Copy its string value in column C (J1.G0203 for example)
Paste it in col/row A2 of Sheet 2.
Loop until all ERROR string values are copied to Col A Sheet 2
Remove the ' from the string value
Add a string $end at the end of the last string value and finally add a string $delete in A1.



=========================
Sheet1 (error_import) raw data:

(ColA) ---------------------------------------------------------------------------------------------------------------------------------(ColB)------------------ (ColC)-----------------------

[TABLE="width: 1117"]
<tbody>[TR]
[TD]CALCUB_RYSL[/TD]
[TD]J1.C1522[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CALCUB_TDRIO[/TD]
[TD]J1.C1511[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CALCUB_VN15[/TD]
[TD]J1.E1512[/TD]
[TD]J1.E1612[/TD]
[/TR]
[TR]
[TD]CALCUB_VP5[/TD]
[TD]C1_EP.1[/TD]
[TD]D5_EP.3[/TD]
[/TR]
[TR]
[TD]CALCUB_VP12[/TD]
[TD]J1.E1621[/TD]
[TD]J1.E1721[/TD]
[/TR]
[TR]
[TD]CALCUB_VP15[/TD]
[TD]J1.E1511[/TD]
[TD]J1.E1611[/TD]
[/TR]
[TR]
[TD]CALCUB_VP24[/TD]
[TD]J1.E1322[/TD]
[TD]J1.E1422[/TD]
[/TR]
[TR]
[TD]CH000_HV[/TD]
[TD]J1.G0203[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]^[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ERROR(SPMHNI-139):[/TD]
[TD]Pin[/TD]
[TD]J1.G0203'[/TD]
[/TR]
[TR]
[TD]not[/TD]
[TD]reconnected.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-------------------------------------------------------------------------------[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CH004_HV[/TD]
[TD]J1.G0207[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]^[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ERROR(SPMHNI-139):[/TD]
[TD]Pin[/TD]
[TD]'J1.G0207'[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]not[/TD]
[TD]reconnected.[/TD]
[/TR]
[TR]
[TD]-------------------------------------------------------------------------------[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CH032_HV[/TD]
[TD]J1.E0203[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]^[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ERROR(SPMHNI-139):[/TD]
[TD]Pin[/TD]
[TD]'J1.E0203'[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]not[/TD]
[TD]reconnected.[/TD]
[/TR]
[TR]
[TD]-------------------------------------------------------------------------------[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CH036_HV[/TD]
[TD]J1.E0207[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]^[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ERROR(SPMHNI-139):[/TD]
[TD]Pin[/TD]
[TD]'J1.E0207'[/TD]
[/TR]
[TR]
[TD]reconnected.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-------------------------------------------------------------------------------[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CH064_HV[/TD]
[TD]J1.A0203[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]^[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ERROR(SPMHNI-139):[/TD]
[TD]Pin[/TD]
[TD]'J1.A0203'[/TD]
[/TR]
[TR]
[TD]not[/TD]
[TD]reconnected.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1117"]
<tbody>[TR]
[TD]...
==================

Sheet2 Col A

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]$delete[/TD]
[/TR]
[TR]
[TD]J1.G0203[/TD]
[/TR]
[TR]
[TD]J1.G0207[/TD]
[/TR]
[TR]
[TD]J1.E0203[/TD]
[/TR]
[TR]
[TD]J1.E0207[/TD]
[/TR]
[TR]
[TD]$end[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance.
Eddie

 

[wideboydixon]

Public Sub FindErrors()

Dim lastRow As Long
Dim thisRow As Long
Dim nextRow As Long

' Clear out the contents of Sheet2
Sheets("Sheet2").Range("A:A").ClearContents

' Find the last row of data on Sheet1
lastRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row

' Next row to fill on Sheet2
nextRow = 1

' Process all rows on Sheet1
For thisRow = 1 To lastRow
    ' Check for "ERROR" at the start of the value in column A
    If Left(Sheets("Sheet1").Cells(thisRow, "A").Value, 5) = "ERROR" Then
        ' Move to the next row on Sheet2
        nextRow = nextRow + 1
        
        ' Put the value into the next row in Sheet2
        Sheets("Sheet2").Cells(nextRow, "A").Value = Replace(Sheets("Sheet1").Cells(thisRow, "C").Value, "'", "")
    End If
Next thisRow

' Add the final "$end" cell
nextRow = nextRow + 1
Sheets("Sheet2").Cells(nextRow, "A").Value = "$end"

' Add the starting "$delete" cell
Sheets("Sheet2").Range("A1").Value = "$delete"

End Sub

WBD

 

[eddiegb1]

Nice wideboydixon! That was quick!

Your code works like a charm.

And thanks for the quick reply.

Regards,
Eddie