Search From Specified Sheet

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Search From Specified Sheet

Post by adam »

Hi anyone,

How could I make the following code to search parameters from the sheet "MySheet" instead of the active sheet?

Code: Select all

Private Sub SearchBtn_Click()

    Dim SearchTerm As String
    Dim SearchColumn As String
    Dim RecordRange As Range
    Dim FirstAddress As String
    Dim FirstCell As Range
    Dim RowCount As Integer
    
    ' Display an error if no search term is entered
    If BillNo.Value = "" And OPNo.Value = "" And RName.Value = "" And ContactNo.Value = "" Then
    
        MsgBox "No search term specified", vbCritical + vbOKOnly
        Exit Sub
    
    End If
    
    ' Work out what is being searched for
    If BillNo.Value <> "" Then
    
        SearchTerm = BillNo.Value
        SearchColumn = "Bill No."
        
    End If
    
    If OPNo.Value <> "" Then
    
        SearchTerm = OPNo.Value
        SearchColumn = "OP Number"
        
    End If

    If RName.Value <> "" Then
    
        SearchTerm = RName.Value
        SearchColumn = "Recipient Name"
        
    End If

    If ContactNo.Value <> "" Then
    
        SearchTerm = ContactNo.Value
        SearchColumn = "Contact No."
        
    End If
    
    Results.Clear
    
        ' Only search in the relevant table column i.e. if somone is searching Location
        ' only search in the Location column
        With Range("Table2[" & SearchColumn & "]")

            ' Find the first match
            Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)

            ' If a match has been found
            If Not RecordRange Is Nothing Then

                FirstAddress = RecordRange.Address
                RowCount = 0

                Do
                
                    ' Set the first cell in the row of the matching value
                    Set FirstCell = Range("A" & RecordRange.Row)
                    
                    ' Add matching record to List Box
                    Results.AddItem
                    Results.List(RowCount, 0) = FirstCell(1, 1)
                    Results.List(RowCount, 1) = FirstCell(1, 2)
                    Results.List(RowCount, 2) = FirstCell(1, 3)
                    Results.List(RowCount, 3) = FirstCell(1, 4)
                    Results.List(RowCount, 4) = FirstCell(1, 5)
                    Results.List(RowCount, 5) = FirstCell(1, 6)
                    Results.List(RowCount, 6) = FirstCell(1, 7)
                    RowCount = RowCount + 1
                    
                    ' Look for next match
                    Set RecordRange = .FindNext(RecordRange)

                    ' When no further matches are found, exit the sub
                    If RecordRange Is Nothing Then

                        Exit Sub

                    End If

                ' Keep looking while unique matches are found
                Loop While RecordRange.Address <> FirstAddress

            Else
            
                ' If you get here, no matches were found
                Results.AddItem
                Results.List(RowCount, 0) = "Nothing Found"
            
            End If

        End With

End Sub
Any help on this would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search From Specified Sheet

Post by HansV »

There are two occurrences of Range(...) in the code. Change them to Worksheets("MySheet").Range(...)
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Search From Specified Sheet

Post by adam »

Thanks very much Hans. Much appreciated. I missed that.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Search From Specified Sheet

Post by adam »

I have another query regarding this code.

When the list-box is populated, the first data which starts from row 2 of the sheet gets populated as the last data row to the list-box. What may be the reason for the this?

In other words, the list box doesn't get populated with data as in order of the worksheet.

Any help on this would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search From Specified Sheet

Post by HansV »

What happens if you change

Code: Select all

            Set RecordRange = .Find(SearchTerm, LookIn:=xlValues)
to

Code: Select all

            Set RecordRange = .Find(What:=SearchTerm, After:=.Cells(.Cells.Count), LookIn:=xlValues)
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: Search From Specified Sheet

Post by adam »

Thanks very much for the help. It worked fine.
Best Regards,
Adam

ebmansoor
NewLounger
Posts: 5
Joined: 29 Aug 2021, 12:31

Re: Search From Specified Sheet

Post by ebmansoor »

Hi

How can I activate 2 searches at the same time?

So if insert the first value; it brings up the items and if i insert another value; it filters the filtered table further.

Thanks!

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search From Specified Sheet

Post by HansV »

Welcome to Eileen's Lounge!

You could loop through the items of the list box in reverse order (from the last item to the first item).
If an item doesn't match the new search criteria, remove the item.
Best wishes,
Hans

ebmansoor
NewLounger
Posts: 5
Joined: 29 Aug 2021, 12:31

Re: Search From Specified Sheet

Post by ebmansoor »

Thanks for the feedback but i think my statement was incorrect.

I want a filter to always be applied and displayed.

The dropdown filters should then filter the filtered display.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search From Specified Sheet

Post by HansV »

Could you attach a small sample workbook and indicate what you want to happen? that would make it easier to visualize what you want.
Best wishes,
Hans

ebmansoor
NewLounger
Posts: 5
Joined: 29 Aug 2021, 12:31

Re: Search From Specified Sheet

Post by ebmansoor »

So if you see the form, its displaying the all the data from "Engagement Profile".

I want to show only the engagements where status in "Advanced Capabilities Tasks" is "Completed".

Whilst holding the "Completed" filter, i want to use the filters further in the form.

Attached is the sample file.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search From Specified Sheet

Post by HansV »

That requires a lot of additional code. See the attached version.

D&amp;A Control Centre (version 2) - Copy.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

ebmansoor
NewLounger
Posts: 5
Joined: 29 Aug 2021, 12:31

Re: Search From Specified Sheet

Post by ebmansoor »

Thank you very much! Works perfectly!

If you guys offer services to perform other functions - please let me know as i would surely be interested.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search From Specified Sheet

Post by HansV »

We're a discussion forum, not a software developer, so we don't "offer services".
Best wishes,
Hans

ebmansoor
NewLounger
Posts: 5
Joined: 29 Aug 2021, 12:31

Re: Search From Specified Sheet

Post by ebmansoor »

Noted. Apologies if it came out rudely - surely it was not written with that intention

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Search From Specified Sheet

Post by HansV »

OK, no problem. Feel free to ask more questions!
Best wishes,
Hans