Search Partial Match and return results on form

I have a form that I need to look down a column of SKU's for partial matches and display each instance on the form. The SKU's are made up of different part numbers for easy reference on what the SKU is. The partial match can be anywhere in the SKU.

For example....using the "Inventory Search" form if you put "6353" in the search box then I want it to return and display on the form each SKU that has that part number in it.

Attached is a sample database.

Thank you,

Your Button "Send SKU's to Sheet" means where in Sheet1?

It can go to sheet2 A1. I'll then modify to the working database accordingly. The code for a partial match is my biggest issue.

Thank you.

This will put the data into the text boxes

Private Sub CommandButton1_Click()
Dim c As Range, i As Long
Dim sh1 As Worksheet
Set sh1 = Worksheets("Sheet1")
i = 0
    For Each c In sh1.Range("B4:B" & sh1.Cells(Rows.Count, 2).End(xlUp).Row)
        If InStr(c, TextBox1) <> 0 Then
        i = i + 1
            Me.Controls("sku_" & i & "_txtbx") = c.Value
            Me.Controls("asin_" & i & "_txtbx") = c.Offset(, 1).Value
            Me.Controls("desc_" & i & "_txtbx") = c.Offset(, 2).Value
        End If
    Next c
End Sub

Put that into Sheet2 right away (part of this code) or only if using the other command button?

If you want the data in Sheet2 while populating the various textboxes in the userform, put this right before the "End If" line

sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 3) = c.Resize(, 3).Value

And

Dim sh2 As Workbook
Set sh2 = Worksheets("Sheet2")

with the other Dim and Set statements.

so you'll end up with

Private Sub CommandButton1_Click()
Dim c As Range, i As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
i = 0
    For Each c In sh1.Range("B4:B" & sh1.Cells(Rows.Count, 2).End(xlUp).Row)
        If InStr(c, TextBox1) <> 0 Then
        i = i + 1
            Me.Controls("sku_" & i & "_txtbx") = c.Value
            Me.Controls("asin_" & i & "_txtbx") = c.Offset(, 1).Value
            Me.Controls("desc_" & i & "_txtbx") = c.Offset(, 2).Value
            sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 3) = c.Resize(, 3).Value
        End If
    Next c
End Sub

and do away with CommandButton2 or use it for something else.

If that is not what you want, let us know.

Your code worked great and I realized I didn't need the userform. I simply added a textbox to the sheet with a command button and directly populates the "Sku" into the sheet. I then use vlookup against the listed sku results to pull only the information I need for the report.

Here's what the code ended up looking like on my working database. I commented out the "Me.control..." statements:

Private Sub CommandButton2_Click()
'Button on Sku Reports sheet for "part_number" box

Dim c As Range, i As Long
Dim amz_inv As Worksheet
Dim sku_rpt As Worksheet

Set sku_rpt = Worksheets("Sku Report")
Set amz_inv = Worksheets("Amz SKU Inventory")

i = 0
    For Each c In amz_inv.Range("B4:B" & amz_inv.Cells(Rows.Count, 2).End(xlUp).Row)
        If InStr(c, part_number) <> 0 Then
        i = i + 1
            'Me.Controls("sku_" & i & "_txtbx") = c.Value
            'Me.Controls("asin_" & i & "_txtbx") = c.Offset(, 1).Value
            'Me.Controls("desc_" & i & "_txtbx") = c.Offset(, 2).Value
            sku_rpt.Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 1) = c.Resize(, 1).Value
        End If
    Next c

End Sub

I know I already closed this but I simply am trying to add a message "Not Found" if entered part number isn't found. Iv'e tried all I know on messages but can't get it to work with this code. Below is my latest attempt.

Private Sub CommandButton2_Click()
'Button on Sku Reports sheet for "part_number" box

Dim c As Range, i As Long
Dim amz_inv As Worksheet
Dim sku_rpt As Worksheet

Set sku_rpt = Worksheets("Sku Report")
Set amz_inv = Worksheets("Amz SKU Inventory")

    i = 0
    For Each c In amz_inv.Range("B4:B" & amz_inv.Cells(Rows.Count, 2).End(xlUp).Row)
        If InStr(c, part_number) <> 0 Then
        i = i + 1
            'Me.Controls("sku_" & i & "_txtbx") = c.Value
            'Me.Controls("asin_" & i & "_txtbx") = c.Offset(, 1).Value
            'Me.Controls("desc_" & i & "_txtbx") = c.Offset(, 2).Value
            sku_rpt.Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(, 1) = c.Resize(, 1).Value
        End If
        
    Next c
    
    If c Is Nothing Then
    MsgBox "Not Found"
    End If
    

End Sub

I thought that you wanted "SKU", "ASIN" and "Description" copied into the other sheet but by the looks of your changed code, you only want SKU copied across. Is that right?

Yes, and I'm using vlookup on the sheet based on the sku results. In my working database there is a sheet where all the information is being pulled from. It's a thousand times larger than the test workbook I uploaded for this thread. My working database has too much proprietary information to post anywhere publicly. Anyways, now I'm just using a sheet text box with a command button on the sheet. I was complicating it by using a form and so I made it more practical and informative. Your code works great and I appreciate your efforts. Couldn't have done it without you. Now all I need to do is figure out this simple message box!

Try following. Change as required.

Private Sub CommandButton1_Click()
Dim SKUS As String, SKUArr
Dim c As Range
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
    For Each c In sh1.Range("B4:B" & sh1.Cells(Rows.Count, 2).End(xlUp).Row)
        If InStr(c, TextBox1) <> 0 Then
            If SKUS = "" Then SKUS = c.Value Else SKUS = SKUS & "," & c.Value
        End If
    Next c
If SKUS = "" Then MsgBox "No such number.": Exit Sub
SKUArr = Split(SKUS, ",")
sh2.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(SKUArr) + 1) = Application.Transpose(SKUArr)
End Sub

I am content with the code below since it seems so much simpler for me to understand...not that I fully understand it and it's working for my needs. I can somewhat get my way around VBA but I'm certainly not a pro. However, I've encountered an issue where I need to add another field of information (the ASIN) in my Sku Report via the same "part_number" text box on the Sku Report sheet. Since I'm using vlookup and the ASIN is left of the searched part number then vlookup doesn't allow looking left of the column referenced. So I need the code below to also pull the ASIN (Amz SKU Inventory, column A) for each associated Sku it finds. In other words, we're searching column B where my ASIN is in column A and I need the ASIN on my report to go into column E of the Sku Report.

I appreciate your help and patience in getting me through this!

Private Sub CommandButton2_Click()
'Button on Sku Reports sheet for "part_number" box

Dim c As Range, i As Long
Dim amz_inv As Worksheet
Dim sku_rpt As Worksheet

Set sku_rpt = Worksheets("Sku Report")
Set amz_inv = Worksheets("Amz SKU Inventory")

    i = 0
    For Each c In amz_inv.Range("B4:B" & amz_inv.Cells(Rows.Count, 2).End(xlUp).Row)
        If InStr(c, part_number) <> 0 Then
        i = i + 1
            sku_rpt.Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(, 1) = c.Resize(, 1).Value
            
        End If
        
    Next c
    

End Sub

I created a mock database which fully mimics my working database. See attached. Called "Mock Test DB Part Number Search".

To reiterate. When I do a part number search for sku's containing the part number I want the resultant sku's and ASIN to be posted to the Sku Report. For the other fields I'm simply using vlookup formulas from the resulting sku's....maybe not the best way but it's the way I understand it best. I hope the new sample DB makes things easier. I'd also like the message if a part number isn't found. Hopefully the code will be simple enough for me to understand. There's alot of stuff in the suggested codes that have gone way over my head.

In a nutshell, on Sku Report sheet you will enter a part number, hit search button and it will go down column b of Amz SKU Inventory sheet and pull out all the sku's that include the part number. For instance, this SKU example has 4 part numbers: 5901-63780-6353-6356 In my working database there are 7 sku's that include part number 5901.

If you change this line

sku_rpt.Range("D" & Rows.Count).End(xlUp).Offset(1).Resize(, 1) = c.Resize(, 1).Value

to these lines

With sku_rpt.Range("D" & Rows.Count).End(xlUp).Offset(1)
.Value = c.Value
.Offset(, 1).Value = c.Offset(, -1).Value
End With

Does that give you the desired result?

In the meantime, try this and let us know what is missing.

Sub Maybe_So()
Dim invArr, transfArr
Dim c As Range
Dim nr As String, i As Long, j As Long
Dim shInv As Worksheet, shRep As Worksheet
Set shInv = Worksheets("Amz SKU Inventory")
Set shRep = Worksheets("Sku Report")
invArr = Array(2, 1, 20, 21, 24, 25, 28, 29, 36)
nr = Application.InputBox("Partial SKU Number You're Looking For", "Information Required")
If WorksheetFunction.CountIf(shInv.Columns(2), "*" & nr & "*") = 0 Then MsgBox "No such item.": Exit Sub
ReDim transfArr(1 To WorksheetFunction.CountIf(shInv.Columns(2), "*" & nr & "*"), 1 To 9)
j = 0
    For Each c In shInv.Range("B4:B" & shInv.Cells(Rows.Count, 2).End(xlUp).Row)
        If InStr(c, CStr(nr)) <> 0 Then
            j = j + 1
                For i = 1 To 9
                    transfArr(j, i) = shInv.Cells(c.Row, invArr(i - 1)).Value
                Next i
        End If
    Next c
shRep.Cells(Rows.Count, 4).End(xlUp).Offset(1).Resize(UBound(transfArr), 9).Value = transfArr
End Sub

Working like a Champ! Can't find anything else at this point.

Thank you for your magic!

Andy

Thanks for letting us know Andy
Good Luck