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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

I
  • Solved
Listbox on userform to show results in reverse order
2
Replies
16
Views
355
Fuji
F
U
  • Question Question
VBA Search Functionality Issue with Arabic Supplier Names in Excel Help Needed???
Replies
1
Views
41
Peter_SSs
Peter_SSs
J
  • Question Question
VBA Search box and button code
Replies
0
Views
272
jlcodes
J
C
  • Question Question
Need to add to my search worksheet
Replies
1
Views
115
craigwojo
C
N
  • Question Question
ActiveX Option Button to sort Data in another sheet and update conbobox
2 3
Replies
22
Views
344
NUC_N_FUTS2
N

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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