Searching for an Order # in a 30+ column sheet

[Bob32103]

I have a spreadsheet of 30+ columns containing shipping information and the specific order number could be in any one of 7 columns. How can I construct a formula that will search thru the entire spreadsheet or just multiple columns and return the order #? The order #’s are all 7 digit numbers beginning with a 1, 4 or 8.

Thanks.

 

[alansidman]

On the Ribbon, Home-->Editing--->Find & Select-->Find

 

[Bob32103]

Thanks - I don’t think I explained my issue correctly. For example, I want to construct a formula in column C that will return a 7 digit number that begins with a 1,4 or 8 from columns D thru Z.

 

[alansidman]

Option Explicit


Sub FindOrder()
    Dim c As Range, rng As Range
    Dim lr As Long, lrC As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    Set rng = Range("D1:Z" & lr)
    For Each c In rng
        lrC = Range("C" & Rows.Count).End(xlUp).Row
        If InStr(c, 1) = 1 Or InStr(c, 4) = 1 Or InStr(c, 8) = 1 Then
            c.Copy Range("C" & lrC + 1)
        End If
    Next c
End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Bob32103]

Thank you for the code. How can I restrict the number of digits returned to only 7 in length with the starting number being only a 1, 4 or 8?
Right now, I'm getting everything with those numbers regardless of length. Also, how can I get the number that is retuned to be in the same row? For example, if order # 1780000 is in cell P2 it should be returned in D2. Likewise, if the number was in cell T2 it would still be returned in cell D2.

Option Explicit


Sub FindOrder()
    Dim c As Range, rng As Range
    Dim lr As Long, lrC As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    Set rng = Range("D1:Z" & lr)
    For Each c In rng
        lrC = Range("C" & Rows.Count).End(xlUp).Row
        If InStr(c, 1) = 1 Or InStr(c, 4) = 1 Or InStr(c, 8) = 1 Then
            c.Copy Range("C" & lrC + 1)
        End If
    Next c
End Sub

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

 

[Rick Rothstein]

Also, how can I get the number that is retuned to be in the same row? For example, if order # 1780000 is in cell P2 it should be returned in D2. Likewise, if the number was in cell T2 it would still be returned in cell D2.

Can there be more than one 7-digit number starting wieht a 1, 4 or 8 on the same row? If yes, what should be put in the cell in Column D... the first matching number, last matching number or all the number delimited with, say, a comma space?

Also, you said the range to search is Columns D:Z which includes the column you want the result placed in (Column D)... is that correct?

 

[alansidman]

Option Explicit


Sub FindOrder()
    Dim c As Range, rng As Range
    Dim lr As Long, lrC As Long
    Dim cRow As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    Set rng = Range("D1:Z" & lr)
    For Each c In rng
        lrC = Range("C" & Rows.Count).End(xlUp).Row
        cRow = c.Row
        If InStr(c, 1) = 1 Or InStr(c, 4) = 1 Or InStr(c, 8) = 1 Then
        If Len(c) = 7 Then
            c.Copy Range("C" & cRow)
        End If
        End If
    Next c
End Sub

 

[Bob32103]

Can there be more than one 7-digit number starting wieht a 1, 4 or 8 on the same row? If yes, what should be put in the cell in Column D... the first matching number, last matching number or all the number delimited with, say, a comma space?

Also, you said the range to search is Columns D:Z which includes the column you want the result placed in (Column D)... is that correct?

Yes, if I could get all of the 7 digit numbers delimited with a comma space that would be the best but there shouldn't be too many in this situation.

Actually, I would place the result in Column C.

Thanks.

 

[Bob32103]

Thanks - that works great!

How would I handle the same seven digit orders that have text before or after them - such as PO #4000000 or 8000000 Trsf. I would just want the seven digit string and not the text.

Option Explicit


Sub FindOrder()
    Dim c As Range, rng As Range
    Dim lr As Long, lrC As Long
    Dim cRow As Long
    lr = Range("D" & Rows.Count).End(xlUp).Row
    Set rng = Range("D1:Z" & lr)
    For Each c In rng
        lrC = Range("C" & Rows.Count).End(xlUp).Row
        cRow = c.Row
        If InStr(c, 1) = 1 Or InStr(c, 4) = 1 Or InStr(c, 8) = 1 Then
        If Len(c) = 7 Then
            c.Copy Range("C" & cRow)
        End If
        End If
    Next c
End Sub

 

[Rick Rothstein]

Yes, if I could get all of the 7 digit numbers delimited with a comma space that would be the best but there shouldn't be too many in this situation.

How would I handle the same seven digit orders that have text before or after them - such as PO #4000000 or 8000000 Trsf. I would just want the seven digit string and not the text.

You should have mentioned that your order numbers could have other text in the cell with them and that there could be more than one order number per row in your first message. See if this macro does what you want...

[table="width: 500"]
[tr]
	[td]Sub FindOrders()
  Dim R As Long, C As Long, X As Long, Data As Variant, Result As Variant
  Data = Range("D1:Z" & Columns("D:Z").Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row)
  ReDim Result(1 To UBound(Data), 1 To 1)
  For R = 1 To UBound(Data, 1)
    For C = 1 To UBound(Data, 2)
      Data(R, C) = " " & Data(R, C) & " "
      If Data(R, C) Like "*[!0-9][148]######[!0-9]*" Then
        For X = 1 To Len(Data(R, C))
          If Mid(Data(R, C), X, 9) Like "[!0-9][148]######[!0-9]" Then
            Result(R, 1) = Result(R, 1) & ", " & Mid(Data(R, C), X + 1, 7)
          End If
        Next
        Result(R, 1) = Mid(Result(R, 1), 3)
      End If
    Next
  Next
  Range("C1").Resize(UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]