Search Column in one sheet for a specific format and output the found values to a column in another work sheet.

[Scrandy]

So I have two worksheets. One with raw data imported from a .txt file and another that I plan to use to organize and pull the data I want. I would use Access but the formatting of the .txt file does not allow for easy importing.

I am trying to search the Cells in Column Data!A:A for product style numbers that either match the format "#####-#### #####" or are 16 characters long (it doesn't matter which). Then i would like for it to output the found values in the "A" Column of the Report Worksheet starting at row 2.

I have made many attempts but nothing has proven even the least bit successful so I won't even bother posting my code.

Any help is greatly appreciated.

 

[Scrandy]

This is the sample data containing the Column I am trying to move into the Report Worksheet. An example of the Cell I would like to find and then copy to the Report Sheet would be Cell "A15".

Thank you again for any suggestions.

 

[VoG]

Try

Sub CopyA()
Dim LR As Long, i As Long
With Sheets("Data")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("A" & i)
            If Len(.Value) = 16 Then .Copy Destination:=Sheets("Report").Range("a" & Rows.Count).End(xlUp).Offset(1)
        End With
    Next i
End With
End Sub

 

[Rick Rothstein]

Give this macro a try (make sure the assigned constants in the Const statements match you actual setup)...

Sub MoveProductStyleNumbers()
  Dim X As Long, LastRow As Long
  Const StartRow As Long = 1
  Const DataSheetName As String = "Data"
  Const ReportSheetName As String = "Report"
  Worksheets(ReportSheetName).Columns("A").NumberFormat = "@"
  With Worksheets(DataSheetName)
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    For X = StartRow To LastRow
      If .Cells(X, "A").Value Like String(16, "#") Or .Cells(X, "A").Value Like "#####-#### #####" Then
        .Cells(X, "A").EntireRow.Copy Worksheets(ReportSheetName).Cells(Rows.Count, "A").End(xlUp).Offset(1)
      End If
    Next
  End With
End Sub

 

[Scrandy]

I was actually on the right track and both of those codes look like they should work so thank you.

However, when i run each of them i get run-time error '13' Type Mismatch for both of them...

For Rick's it is line:

If .Cells(X, "A").Value Like String(16, "#") Or .Cells(X, "A").Value Like "#####-#### #####" Then

For Vog's it is line:

If Len(.Value) = 16 Then .Copy Destination:=Sheets("Report").Range("a" & Rows.Count).End(xlUp).Offset(1)

Any idea why this would occur?

Again thank-you for your help and suggestions

 

[VoG]

Can you use one of these methods to post a sample of your data so that we can copy and paste into Excel in order to test http://www.mrexcel.com/forum/showthread.php?t=508133

 

[Rick Rothstein]

Can you use one of these methods to post a sample of your data so that we can copy and paste into Excel in order to test http://www.mrexcel.com/forum/showthread.php?t=508133

@Peter...

Actually, I'm thinking a copy of the data might not be sufficient as I do not see how either of our codes could produce the error the OP has reported.

@Scrandy...

Any chance you can post the workbook itself at one of the free online file sharing sites? You can post it online using one of these free posting websites...

Box: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
FileDropper: http://www.filedropper.com
RapidShare: http://www.rapidshare.com

Then post the URL they give you for the file back here.

 

[Scrandy]

Here is the link to the workbook: http://www.box.com/s/uqsuujr551z664llsd8t

 

[VoG]

Try

Sub CopyA()
Dim LR As Long, i As Long
With Sheets("Data")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If Not IsError(Range("A" & i)) Then
            With .Range("A" & i)
                If Len(.Value) = 16 Then .Copy Destination:=Sheets("Report").Range("a" & Rows.Count).End(xlUp).Offset(1)
            End With
        End If
    Next i
End With
End Sub

 

[Rick Rothstein]

Here is the link to the workbook: http://www.box.com/s/uqsuujr551z664llsd8t

Okay, the problem is you have one formula in Column A on the Data sheet and it is evaluating to a #NAME? error. The formula is this...

=fStyle(Data!A:A)

We can write code to step around this problem, but your original description said the Data sheet contained raw imported data... if that is the case, then how did an active formula get into that cell? Is it correct to have any formulas in Column A on the Data sheet? Or is that left over from something else you were trying to do? If you remove that one formula, the code I posted (and I presume Peter's code) will work as intended.