Search Based on Multiple Creteria

PULBAG

New Member
Joined
Sep 24, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I've created a search tool and a data source each is a separate Excel file. Below search code looks in de data source for a certain value (user input value in number or text format) and returns with a copy of the entire row where the data is found. The copied information is then showed in the search file. At the moment the code uses input value from a single cell. In this case the cell is called “FILE_NAME”.
The intention is to have the code such that it uses the input of multiple cells, only filled cells, and returns with the rows corresponding to the input data.
Appreciate any help!

VBA Code:
Sub Search()
  Call Searchfunction
End Sub

Sub Searchfunction()

Application.ScreenUpdating = False

'define workbook and worksheet to search.
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet

'Open wb1 and ws1 and set (SEARCH workbook)
Set wb1 = ActiveWorkbook
Set ws1 = ActiveSheet

'Open wb2 and ws2 and set (Data File)
 Set wb2 = Workbooks.Open("C:\Users\Paul Baghramian\OneDrive - APT CuttingB.V\APTC_Engineering\99-APTC-Drawing List\APT_Cutting_Drawing_List.xlsx")
 Set ws2 = wb2.Worksheets("APT Drawing List")

'empty output range
 ws1.Range("8:250") = ""

'get search value
 searchvalue = ws1.Range("FILE_NAME").Value = Range("FILE_NAME").Value
  
'define outputrow startnumber
 outputrow = 8

  'start searching for the value is column 1  (A to H) and rows 4 to 1000.
   For Column = 1 To 14

    For Row = 2 To 2000
    
    'get text from cell
    text_in_cell = ws2.Cells(Row, Column)
        
            'convert both the text in the cell as well as the searchvalue to lowercase
            text_in_cell = LCase(text_in_cell)
            'searchvalue = LCase(searchvalue)
    
        'if text contains searchvalue
        If InStr(text_in_cell, searchvalue) Then
            
            'copy the specific row A to H from sourcerange srange on source sheet to destination range drange on destination sheet Blad1
            Let srange = "A" & Row & ":" & "N" & Row
            Let drange = "A" & outputrow & ""
            ws2.Range(srange).Copy _
                Destination:=ws1.Range(drange)
            
            'go to next output row
            outputrow = outputrow + 1
        
        End If
    
    Next Row

 Next Column

'close dataset.
 wb2.Close

'all is done, go back to SEARCH sheet
 ws1.Activate

   Application.ScreenUpdating = True

End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,148,277
Messages
5,745,818
Members
423,980
Latest member
zimza

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
Top