Search and show multiple results on another sheet

J

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
172
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]
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
johnsonk said:
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
Click to expand...


Try this

Code: 
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
 
Upvote 0
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]
 
Upvote 0
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


[TABLE="width: 1626"]
<colgroup><col><col span="2"><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 1626"]
<colgroup><col><col span="2"><col><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]PLU[/TD]
[TD]DESCRIPTION[/TD]
[TD]PLU MATCH[/TD]
[TD]BATCH[/TD]
[TD]QTY kg[/TD]
[TD]TEMP 0°c[/TD]
[TD]USE BY[/TD]
[TD]RETAIL AREA[/TD]
[TD]HOLDING AREA[/TD]
[TD]IN / OUT[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]UNSALTED BUTTER ROASTED ONION[/TD]
[TD]29/03/1908[/TD]
[TD]02/05/1900[/TD]
[TD]10[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]OUT[/TD]
[TD]09/07/2019[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]123[/TD]
[TD]10/01/1900[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]OUT[/TD]
[TD]09/07/2019 09:23[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]UNSALTED BUTTER ROASTED ONION[/TD]
[TD]3011[/TD]
[TD]123[/TD]
[TD]29/02/1900[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]OUT[/TD]
[TD]09/07/2019[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]123[/TD]
[TD]60[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]OUT[/TD]
[TD]09/07/2019 09:16[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]UNSALTED BUTTER ROASTED ONION[/TD]
[TD]3011[/TD]
[TD]123[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]OUT[/TD]
[TD]09/07/2019[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]123[/TD]
[TD]25[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]OUT[/TD]
[TD]09/07/2019 08:40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]UNSALTED BUTTER ROASTED ONION[/TD]
[TD]3011[/TD]
[TD]321[/TD]
[TD]30/01/1900[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]IN[/TD]
[TD]09/07/2019[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]321[/TD]
[TD]30[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]IN[/TD]
[TD]09/07/2019 08:28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]UNSALTED BUTTER ROASTED ONION[/TD]
[TD]3011[/TD]
[TD]123[/TD]
[TD]30[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]IN[/TD]
[TD]09/07/2019[/TD]
[/TR]
[TR]
[TD]3011[/TD]
[TD]123[/TD]
[TD]30[/TD]
[TD]1[/TD]
[TD]09/07/2019[/TD]
[TD]you[/TD]
[TD]me[/TD]
[TD]IN[/TD]
[TD]09/07/2019 08:24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
johnsonk said:
Just notice the results in green are also messed up.
Click to expand...


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA Search box and button code
Replies
0
Views
247
jlcodes
J
C
  • Question Question
Need to add to my search worksheet
Replies
1
Views
104
craigwojo
C
I
  • Question Question
Receive Automation error when running code & values added to sheet twice
2
Replies
13
Views
430
NoSparks
N
P
  • Solved
Button produces different result
Replies
3
Views
299
RoryA
RoryA
M
  • Question Question
VBA if i cancel the update it clears the sheet
2
Replies
10
Views
772
malveiro
M

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top