Search and show multiple results on another sheet

[johnsonk]

Hi,
I have a database that has 11 columns in one sheet and I have created a search in another sheet with a search button, clear button and print button it works but it only shows one result and not multiple for example I could have more than one of the same code in column A so I need it to show all results with the same code. here is what I have at the minute.

Module 1
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub searchdata()
On Error GoTo MyerrorHandler:
Dim PLUnumber As Long
itemcode = Cleardata.Range("D10")
Set MyRange = PackData.Range("A:K")
If Range("D10") = "" Then
MsgBox "You didn?t enter any PLU number!"
Exit Sub
End If
Range("A13").Value = Range("D10").Value
Range("B13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 2, False)
Range("C13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 3, False)
Range("D13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 4, False)
Range("E13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 5, False)
Range("F13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 6, False)
Range("G13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 7, False)
Range("H13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 8, False)
Range("I13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 9, False)
Range("J13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 10, False)
Range("K13").Value = Application.WorksheetFunction.VLookup(itemcode, MyRange, 11, False)
MyerrorHandler:
If Err.Number = 1004 Then
MsgBox "PLU number does not exist!"
End If
End Sub
[/FONT]
Module 2
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Cleardata()
Range("D10").Clear
Range("A13:K40").Clear
Range("D10").Select
End Sub

[/FONT]Module 3
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub printdata()
Dim itemcode As Long
itemcode = Cleardata.Range("D10")
myselection = MsgBox("Are you sure you want to print?", vbOKCancel, "ALERT")
Cleardata.Range("A1:K40").PrintPreview
Cleardata.Range("A1:K40").PrintOut
End Sub

Regards[/FONT]

 

[DanteAmor]

Hi,
I have a database that has 11 columns in one sheet and I have created a search in another sheet with a search button, clear button and print button it works but it only shows one result and not multiple for example I could have more than one of the same code in column A so I need it to show all results with the same code. here is what I have at the minute.

Module 1

Try this

Sub searchdata()
    Dim [COLOR=#008000]itemcode [/COLOR]As Variant, i As Long
    Dim r As Range, f As Range, cell As String
    
    [COLOR=#008000]itemcode [/COLOR]= Cleardata.Range("D10").Value
    If [COLOR=#008000]itemcode [/COLOR]= "" Then
        MsgBox "You didnt enter any PLU number!"
        Exit Sub
    End If
    
    Set r = PackData.Range("A:K")
    Set f = r.Find([COLOR=#008000]itemcode[/COLOR], LookIn:=xlValues, lookat:=xlWhole)
    If Not f Is Nothing Then
        cell = f.Address
        i = 13
        Do
            Cleardata.Range("A" & i).Resize(1, 11).Value = f.Resize(1, 11).Value
            i = i + 1
            Set f = r.FindNext(f)
        Loop While Not f Is Nothing And f.Address <> cell
        MsgBox "Done"
    Else
        MsgBox "PLU number does not exist!"
    End If
End Sub

 

[johnsonk]

That works great thank you

 

[DanteAmor]

That works great thank you

I'm glad to help you. Thanks for the feedback.

 

[johnsonk]

Hi
when i am doing my search it seems to duplicate any ideas?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Private Sub cmd_Search_Click()
Dim itemcode As Variant, i As Long
Dim r As Range, f As Range, cell As String

itemcode = Sheet10.Range("C10").Value
If itemcode = "" Then
MsgBox "You did not enter any PLU number!"
Exit Sub
End If

Set r = Sheet8.Range("A:K")
Set f = r.Find(itemcode, LookIn:=xlValues, LookAt:=xlWhole)
If Not f Is Nothing Then
cell = f.Address
i = 13
Do
Sheet10.Range("A" & i).Resize(1, 11).Value = f.Resize(1, 11).Value
i = i + 1
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
MsgBox "Search Complete"
Else
MsgBox "PLU number does not exist!"
End If
End Sub[/FONT]

 

[DanteAmor]

Hi
when i am doing my search it seems to duplicate any ideas?

Check your data, maybe you have the data several times on Sheet8 with the same data

 

[johnsonk]

There could be more than one result with the same plu, see below an example search result for PLU 3011 there should only be 5 results the correct one's are highlighted in green the others i'm not quite sure what is happening

PLU DESCRIPTION PLU MATCH BATCH QTY kg TEMP 0°c USE BY RETAIL AREA HOLDING AREA IN / OUT DATE 3011 UNSALTED BUTTER ROASTED ONION 29/03/1908 02/05/1900 10 1 09/07/2019 you me OUT 09/07/2019 3011 123 10/01/1900 1 09/07/2019 you me OUT 09/07/2019 09:23 3011 UNSALTED BUTTER ROASTED ONION 3011 123 29/02/1900 1 09/07/2019 you me OUT 09/07/2019 3011 123 60 1 09/07/2019 you me OUT 09/07/2019 09:16 3011 UNSALTED BUTTER ROASTED ONION 3011 123 25 1 09/07/2019 you me OUT 09/07/2019 3011 123 25 1 09/07/2019 you me OUT 09/07/2019 08:40 3011 UNSALTED BUTTER ROASTED ONION 3011 321 30/01/1900 1 09/07/2019 you me IN 09/07/2019 3011 321 30 1 09/07/2019 you me IN 09/07/2019 08:28 3011 UNSALTED BUTTER ROASTED ONION 3011 123 30 1 09/07/2019 you me IN 09/07/2019 3011 123 30 1 09/07/2019 you me IN 09/07/2019 08:24 <colgroup><col><col span="2"><col><col><col><col><col span="2"><col><col></colgroup><tbody> </tbody> <strike></strike>

<colgroup><col><col span="2"><col><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[johnsonk]

Just notice the results in green are also messed up.

 

[DanteAmor]

Just notice the results in green are also messed up.

You could upload a copy of your file to a free site such 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. If the workbook contains confidential information, you could replace it with generic data.