Add additional search function

Hi Experts,

I would like to request Experts help to extent of the search data function at Sheet 1 (DataUpdate). The current macro able to trace and displayed all data from Column D (MasterData workbook) based on entered data at cell C2 (DataUpdate).

The search function need to extent also at Column A and E from MasterData workbook. If any  data entered in Cell C2 (DataUpdate sheet) and the data available in MasterData workbook (at column A, D oe E), the matched data will be displayed at DataUpdate sheet.

I have attached the workbooks for Experts perusal. Hope Experts will help me to add this additional function.
SearchData2.xls
MasterData.xls
CartilloAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Try this in the worksheet module

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Me.UsedRange.Offset(4, 0).ClearContents
nr = 5
Set mwb = Workbooks("masterdata").Worksheets("masterlist")
For Each rw In mwb.UsedRange.Rows
For Each cel In rw.Cells
If Target.Value = cel.Text Then
cel.EntireRow.Copy Me.Cells(nr, 1)
nr = nr + 1
Exit For
End If
Next cel
Next rw
Application.EnableEvents = True
End Sub
0
apresenceCommented:
Okay, I added your changes.  You can easily add/change the fields you want to search with the following.  Note that you need to specify the column number rather than letter:
'- Which fields to search
Private Const SEARCH_FIELDS = "1,4,5"

Open in new window


Drop the updated code in your worksheet, replacing the previous code:
Option Explicit

Private m_oMasterDataSheet As Worksheet

'- Which fields to search
Private Const SEARCH_FIELDS = "1,4,5"

'- This routine loads the master data sheet into m_oMasterDataSheet and keeps it there
Private Sub LoadMasterDataSheet()
    Dim oBook As Workbook

    '- If we've already loaded it, there's nothing to do
    If (Not (m_oMasterDataSheet Is Nothing)) Then Exit Sub
    
    '- Open the "MasterData" worksheet; This assumes it's in the same directory as the current worksheet
    Set oBook = Workbooks.Open(Me.Parent.Path & "\MasterData.xls")
    
    '- We're interested in just the first data sheet
    Set m_oMasterDataSheet = oBook.Sheets.Item(1)
    
    '- Switch focus back to us
    Call Me.Activate
End Sub

'- This routine converts a Variant to a Long and returns -1 on error
Private Function VarToLong(Value As Variant) As Long
    VarToLong = -1
    
    On Error Resume Next
    VarToLong = CLng(Value)
End Function

'- Compares two values:
'- * If they are numeric, converts to a Long to allow eqivalent values like "77777" and "077777" to match
'- * If they are not, do a case-insensitive comparison
Private Function KeyCompare(Key As Variant, Value As Variant) As Boolean
    Dim lKey As Long
    Dim lValue As Long
    
    '- Try a direct comparison first
    If (Key = Value) Then
        KeyCompare = True
        Exit Function
    End If
    
    '- Try numeric comparison next
    lKey = VarToLong(Key)
    If (lKey <> -1) Then
        lValue = VarToLong(Value)
        If (lValue <> -1) Then
            KeyCompare = (lKey = lValue)
            Exit Function
        End If
    End If
    
    '- Didn't work, try case-insensitive text comparison
    On Error Resume Next
    KeyCompare = (StrComp(LCase(Key), LCase(Value), vbTextCompare) = 0)
End Function

'- This routine searches the master data sheet for "Key"
Private Function DoSearch(Key As Variant) As Boolean
    Dim nSrcRow As Long
    Dim nDstRow As Long
    Dim oSrcRange As Range
    Dim oDstRange As Range
    Dim vValue As Variant
    Dim nCol As Long
    Dim aSearchFields As Variant
    Dim nIdx As Long
    Dim bMatch As Boolean
    
    nSrcRow = 4 '- Search data begins on row 4
    nDstRow = 5 '- Results begin on row 5
    aSearchFields = Split(SEARCH_FIELDS, ",") '- Get an array of search fields/columns
    Do
        '- For each search field/column...
        bMatch = False
        For nIdx = LBound(aSearchFields) To UBound(aSearchFields)
            '- Get the column number
            nCol = CLng(aSearchFields(nIdx))
            
            '- Get the value for the current row at that column
            vValue = m_oMasterDataSheet.Cells(nSrcRow, nCol).Value
        
            '- First field with empty/blank value indicates end of source data
            If (nIdx = LBound(aSearchFields)) And ((IsEmpty(vValue)) Or (Len(vValue) = "")) Then Exit Do
            
            '- Compare them
            If (KeyCompare(Key, vValue)) Then
                '- At least one field matched, we don't need to check the rest...
                bMatch = True
                Exit For
            End If
        Next
        
        '- If at least one field matched...
        If (bMatch) Then
            '- We want all 5 columns of the matching row from the source
            Set oSrcRange = m_oMasterDataSheet.Range(m_oMasterDataSheet.Cells(nSrcRow, 1), m_oMasterDataSheet.Cells(nSrcRow, 5))
            Set oDstRange = Range(Cells(nDstRow, 1), Cells(nDstRow, 5))
            
            '- Copy the data
            Call oSrcRange.Copy(oDstRange)
            
            '- Progress the destination row
            nDstRow = nDstRow + 1
            
            '- Flag that we found one or more matches
            DoSearch = True
        End If
        
        '- Progress the source row
        nSrcRow = nSrcRow + 1
    Loop
End Function

'- This routine responds to changes in the searchdata sheet and performs a lookup if we're in the search cell
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim bFound As Boolean

    '- Disable events: We're not interested in getting updates while we're changing things
    Application.EnableEvents = False
    
    If (Target.Address = "$C$2") Then
        '- Move back to the input box
        If (ActiveCell.Address <> Target.Address) Then Call Target.Select
        
        '- Clear the existing results
        '-   Our results start at row 5 and consist of 5 columns
        Range(Cells(5, 1), Cells(UsedRange.Rows.Count, 5)).Clear
        
        '- Load the master sheet
        Call LoadMasterDataSheet
        
        '- Disable screen updates/calculations (Speeds things up)
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        
        '- Do the search/copy results
        bFound = DoSearch(Target.Value)
        
        '- Re-enable screen updates/calcuations
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
    End If
    
    '- Show not found dialog if we didn't find anything
    If (bFound = False) Then
        MsgBox "Search Data Not Found"
    End If
    
    '- Re-enable events
    Application.EnableEvents = True
End Sub

'- Quick macro to re-enable things in case we had to stop the debugger before we could
Private Sub EnableEvents()
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window


Or just use the new copy:
SearchData-2.0.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CartilloAuthor Commented:
Hi apresence,

Thanks for the code. Is that possible to set the MasterData path, planning to save the workbook in a network folder "\\o3share\data\" and my searchdata will be copied at users desktop.
 
Set oBook = Workbooks.Open(Me.Parent.Path & "\MasterData.xls")

After copied the data can we closed the MasterData workbook?

0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

apresenceCommented:
Sure, just change this line:
Set oBook = Workbooks.Open(Me.Parent.Path & "\MasterData.xls")

Open in new window


To this:
Set oBook = Workbooks.Open("\\o3share\data\MasterData.xls")

Open in new window

0
CartilloAuthor Commented:
Hi,

Thanks for the code, what line I need to add if I want to close MasterData workbook after the data  has been update into SearchData wb. Please advice.
0
apresenceCommented:
This procedure will do it:
'- This routine closes the master data sheet in m_oMasterDataSheet
Private Sub CloseMasterDataSheet()
    '- If we haven't loaded it, there's nothing to do
    If (m_oMasterDataSheet Is Nothing) Then Exit Sub
    
    '- We have to close the workbook that contains the worksheet
    Call m_oMasterDataSheet.Parent.Close
    Set m_oMasterDataSheet = Nothing
End Sub

Open in new window


If you wish to close it immediately after each search, then modify this line of Worksheet_Change:
bFound = DoSearch(Target.Value)

Open in new window


To be this:
bFound = DoSearch(Target.Value)
Call CloseMasterDataSheet

Open in new window

0
CartilloAuthor Commented:
Hi apresence,

Thanks a lot for the superb solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.