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]

@Rick

My apologies, that formula should not be there. Just a residual error from my attempts at trying to write a code. But when that formula is taken out your code works great. I greatly appreciate the help.

The macro also seemed to output the values "4826" in Column B and "-----" in column D which repeated in every row that had a value generated in Column A which I assumed was from the ".Cells(X, "A").EntireRow.Copy" portion of your if statement. So I tweaked it to read:

If CellValue Like String(16, "#") Or Left(CellValue, 10) Like "#####-####" Then
.Cells(X, "A").Copy Worksheets(ReportSheetName).Cells(Rows.Count, "A").End(xlUp).Offset(1)
End If

Omitting the ".EntireRow" gave me only the values from "Data!A:A" and then I realized that some of my style numbers were inconsistent with the "#####-#### #####" formatting I believed they all had. So I changed the if statement to evaluate only the first 10 characters of the cell instead of its entire contents.

This code works great and is going to make it a lot easier for me to pull the rest of the necessary data I need for this report.

Thank you for all of your help

 

[Rick Rothstein]

My apologies, that formula should not be there. Just a residual error from my attempts at trying to write a code. But when that formula is taken out your code works great. I greatly appreciate the help.

Yeah, that is what I thought. And, of course, you are quite welcome.

The macro also seemed to output the values "4826" in Column B and "-----" in column D which repeated in every row that had a value generated in Column A which I assumed was from the ".Cells(X, "A").EntireRow.Copy" portion of your if statement.

Correct (I wasn't completely sure what you needed when I wrote that code originally).

So I tweaked it to read:

If CellValue Like String(16, "#") Or Left(CellValue, 10) Like "#####-####" Then

You could also write that line this way...

If CellValue Like String(16, "#") Or CellValue Like "#####-####*" Then

....and then I realized that some of my style numbers were inconsistent with the "#####-#### #####" formatting I believed they all had. So I changed the if statement to evaluate only the first 10 characters of the cell instead of its entire contents.

If you can identify all the ways in which the characters after the first 10 characters vary, we could probably modify the statement to specifically target them. That way you would never false-match something like "12345-6789 Hello", although your data may never actually be able to be structured like that (in which case the first 10 characters check is probably sufficient).

 

[Scrandy]

Followup:

How would I go about making the Style Numbers in Column A of the Report Worksheet to skip every other row that way I would have a blank row between each of them?
I assuming I would have to manipulate the "offset" contained in the "Then" statement so that it applies each time a Style Number is copied.

Also how would I keep a count of which row the on the Report worksheet the current evaluated Style Number is being copied to? I want to use this count in order to reference and to print relative information in same row as the most recently found Style Number.

Again, your help is greatly appreciated

 

[Scrandy]

You could also write that line this way...

If CellValue Like String(16, "#") Or CellValue Like "#####-####*" Then

Thanks for the simplification of that If statement. It makes the code easier to follow.

If you can identify all the ways in which the characters after the first 10 characters vary, we could probably modify the statement to specifically target them. That way you would never false-match something like "12345-6789 Hello", although your data may never actually be able to be structured like that (in which case the first 10 characters check is probably sufficient).

I just checked our systems and all of our style names have the first 10 characters in the format "#####-####", and then the following characters have countless variations, so I am not to concerned about it.

 

[Rick Rothstein]

Followup:

How would I go about making the Style Numbers in Column A of the Report Worksheet to skip every other row that way I would have a blank row between each of them?
I assuming I would have to manipulate the "offset" contained in the "Then" statement so that it applies each time a Style Number is copied.

Also how would I keep a count of which row the on the Report worksheet the current evaluated Style Number is being copied to? I want to use this count in order to reference and to print relative information in same row as the most recently found Style Number.

You could modify the number in the Offset property, changing it from 1 to 2, in order to skip rows on the output; however, in light of your second question, I changed the code a little bit...

Sub MoveProductStyleNumbers()
  Dim X As Long, LastRow As Long, NextOutputRow 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
        NextOutputRow = Worksheets(ReportSheetName).Cells(Rows.Count, "A").End(xlUp).Row + 2
        .Cells(X, "A").Copy Worksheets(ReportSheetName).Cells(NextOutputRow, "A")
      End If
    Next
  End With
End Sub

Inside the If..Then block inside the loop, the NextOutputRow will contain the row number the next output will be written to. I think that will give you what you asked for.