edit the results of a search button

[zfrenchy]

hello all,

I am working on a excel book to track of a warehouse shelves inventory, so far the inventory spreadsheet has 1000 rows.
column are brand/model/description/quantity/shelf/...

On another spreadsheet I made a 'search button', which work very fine and display rows of the results.

I want the ability to edit the results and make change on any case.

Example (see photo):
Using the search button for item brand 'DMP', the results shows multiple row of parts 'xxx' of the brand 'DMP'

I want to be able to remove one item of the parts '710' of the brand 'DMP' from shelf '73'

Next time searching 'DMP' will show 'quantity 2' for part '710'


Here a link to the book
Search inventory V2.xlsm

 

[Micron]

I looked at your file. Seeing as how you don't have any replies I'll just say that if you stick with code I can only think of one way to do this. Obviously you cannot just edit the cell in you pic because Quantity is the result of a formula. So you'll have to edit the Inventory sheet.

First you'd need to choose a way to run this code (sub) and my guess would be the double click on a cell in your Search sheet.
Then pass the values that you require from the dbl clicked row to this sub (from Search sheet). I assume these are Brand, Model and Shelf.
Now on the Inventory sheet:
Then use Find to find the first row with Brand e.g. "DMP" (I'll use variable Frow here for that). Then use Find from the bottom up to get the last row with DMP (Lrow)
Using Find from the bottom up

Then loop from Frow to Lrow and find the row with those Model and Shelf values. If found, reduce the quantity column value.
Finding row using multiple criteria (this example uses x,y,z)

IMO all that requires more thought on your part first.
F'rinstance, what if there are 2 or more rows that are found in that loop? Use a variable for row count if found, and if >1 then what?
You mentioned "removing one". Is that the same as reducing a quantity by 1"?
If yes but not always 1 you need a way to input a value. Perhaps a helper cell or input box.

HTH

 

[Micron]

You could try this on a copy of your wb. It makes assumptions based on the questions I posted so you may have to tweak it. Copy & paste code into your Search sheet module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Frow As Long, Lrow As Long, lngShelf As Long
Dim i As Long, intMatch As Integer, lngFound As Long
Dim wsSearch As Worksheet, wsInv As Worksheet
Dim strBrand As String, strModel As String

If Target.Column <> 6 Then Exit Sub
Cancel = True
Set wsInv = Sheets("Inventory")
Set wsSearch = Sheets("Search")

strBrand = Target.Offset(0, -4)
strModel = Target.Offset(0, -3)
lngShelf = Target

With wsInv.Range("B:B")
    Frow = .Find(strBrand, after:=Cells(1, 2), searchdirection:=xlNext).Row
    Lrow = .Find(strBrand, after:=Cells(1, 2), searchdirection:=xlPrevious).Row
End With

For i = Frow To Lrow
    If wsInv.Range("C" & i) = strModel And wsInv.Range("F" & i) = lngShelf Then
        intMatch = intMatch + 1
        Debug.Print i
        lngFound = i
    End If
Next
Select Case intMatch
    Case 0
        MsgBox "No matching rows were found on inventory sheet"
    Case Is = 1
        MsgBox "Shelf count will be reduced by 1 on Inventory sheet for row " & lngFound
        wsInv.Range("F" & lngFound) = wsInv.Range("F" & lngFound) - 1
    Case Is > 1
        MsgBox "More than 1 match was found. No reduction was performed."
End Select

End Sub