none
Creating a Search Function in Excel RRS feed

  • Question

  • Hi all:

    I am looking to create a search bar for one sheet in excel. The search function would need to be able to search the entire document, and then pull up the entire row for the correlating search item. The search will need to be able to look through both numerical and alphabetical values. I am new to VBA and I am struggling creating a code that does not have any issues in it. Could anyone help me?

    Thanks in advance.

    Thursday, May 31, 2018 4:28 PM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Friday, June 1, 2018 2:51 AM
  • Hello lacee1234,

    You could go loop through each worksheet to do the find action and then copy the entire row to target range. Here is a simply code, you could try to adjust it for your need.

    Sub Test()
    Dim targetSheet As Worksheet
    
    On Error Resume Next
    Set targetSheet = ThisWorkbook.Worksheets("ResultSheet")
    If targetSheet Is Nothing Then
    Set targetSheet = ThisWorkbook.Worksheets.Add
    targetSheet.Name = "ResultSheet"
    End If
    
    'searchStr is what you want to search
    searchStr = "A"
    foundResultCount = 0
    For Each sourceSheet In ThisWorkbook.Worksheets
           If Not sourceSheet.Name = "ResultSheet" Then
           Set foundCell = sourceSheet.UsedRange.Find(searchStr)
           If Not foundCell Is Nothing Then
               AddressStr = foundCell.Address
           End If
           
           Do
              If foundCell Is Nothing Then
                    Exit Do
               Else
                    foundResultCount = foundResultCount + 1
                    foundCell.EntireRow.Copy targetSheet.Cells(foundResultCount, 1)
              End If
              Set foundCell = sourceSheet.Cells.FindNext(After:=foundCell)
           Loop While AddressStr <> foundCell.Address
           End If
    Next sourceSheet
    End Sub

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 1, 2018 5:48 AM
  • Hello lacee1234,

    What's the state of the thread? Is your original issue resolved? Please feel free to let us know if your issue has any update.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 4, 2018 1:35 AM