Search, filter and populate spreadsheet

[Bevilready]

Hello, I have a spreadsheet in which I would like to be able to use a search box to filter through a list of products and add a quantity to each item I search for. After which I'm would like the items I have put quantities against to appear in a separate list, which would become an order to send to my supplier. I have been able to create a search, but am not able to put a quantity in the cell which remains once the search is cleared.
Can anyone help me please?
Thanks,
Bevilready

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your sheets.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Bevilready]

Hi Mumps,
Sorry, I haven't posted on this type of site before.
The dropbox link is below.
Order.xlsx

 

[mumps]

Click here to download your file. Please note that I have deleted the text box and the button as they are not necessary. Cell B2 has a drop down list of all the Tender Items in column B. If you add more Items, the drop down list will adjust automatically. This will save on manually entering the search criterium and also avoid typo's. First enter the size and color in columns D and E and then make a selection in B2. Below is the macro. To view it, right click the tab name for your sheet and click 'View Code'. Close the window to return to your sheet. The macro is triggered automatically when you make a selection in B2 so it is important that you enter size and color in columns D and E before you make the selection in B2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
    Dim qty As String, fnd As Range
    If Target <> "" Then
        Set fnd = Range("B5", Range("B5").End(xlDown)).Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
        qty = InputBox("Enter the quantity for " & Target.Value & ".")
        If qty = "" Then Exit Sub
        Range("E" & fnd.Row) = qty
        Range("A" & fnd.Row).Resize(, 7).Copy Cells(Rows.Count, "N").End(xlUp).Offset(1)
        Target.ClearContents
    End If
End Sub

 

[Bevilready]

Hi Mumps, Thanks for creating the macro. It works fine but I was hoping to be able to search for items like 'Coffee' and for all rows containing 'Coffee' to show. I could then choose how many of each item I wanted to order.

 

[mumps]

Click here for your file. It works in a similar fashion. Enter a search criterium in B2 and press the RETURN key. The data is filtered base on your input. Fill in the Size and Colour and lastly fill in the Quantity and press the RETURN key. The data will be copied to the other list. The copied data may not be visible until you click the "Re-Set" button to clear the filter.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B2,E:E")) Is Nothing Then Exit Sub
    If Target <> "" Then
        Select Case Target.Column
            Case Is = 2
                Range("A4", Range("F" & Rows.Count).End(xlUp)).AutoFilter Field:=2, Criteria1:="*" & Target.Value & "*"
            Case Is = 5
                Range("A" & Target.Row).Resize(, 7).Copy Cells(Rows.Count, "N").End(xlUp).Offset(1)
        End Select
    End If
End Sub

 

[Bevilready]

Thanks Mumps, that works exactly how I want it to. I just need to create a button to clear the cells after each time an order is placed.

 

[Bevilready]

Hi Mumps, I've added two columns to each of the tables to allow for more information. (Category & Stock Code). Can you tell me how the VBA code changes to accommodate it please?

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B2,G:G")) Is Nothing Then Exit Sub
    If Target <> "" Then
        Select Case Target.Column
            Case Is = 2
                Range("A4", Range("I" & Rows.Count).End(xlUp)).AutoFilter Field:=2, Criteria1:="*" & Target.Value & "*"
            Case Is = 7
                Range("A" & Target.Row).Resize(, 9).Copy Cells(Rows.Count, "P").End(xlUp).Offset(1)
        End Select
    End If
End Sub

I just need to create a button to clear the cells after each time an order is placed.

I inserted the button on your sheet in the file I posted.

 

[Bevilready]

Brilliant, thanks for doing that.
I was meaning that I needed to create a button to clear all the data in the cells of the order list. Effectively resetting the sheet after placing the order.