• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 447
  • Last Modified:

Search and Display Data

Hi Experts,

I would like to request Experts help create a macro for search data from MasterData workbook  and displayed at Searchdata workbook (A5:E). Cell C2 at Searchdata Workbook will be used for type the search data. If the search data is available in the MasterData, the whole data columns (A:E) need to display at Searchdata. If not, message box will alert as ”Search Data not found”. I have manually copied sample data at SearchData workbook for Experts to get better view. Hope Experts will help me to create this feature.



SearchData.xls
MasterData.xls
0
Cartillo
Asked:
Cartillo
  • 9
  • 8
  • 4
  • +1
3 Solutions
 
wchhCommented:
Try macro below
Sub Search()
Dim Sht As Worksheet
Dim Shtm As Worksheet
Dim mRow As Long
Dim I As Long
Set Sht = Workbooks("SearchData(1).xls").ActiveSheet
With Sht
    If .UsedRange.Rows.Count > 4 Then
        .Rows("5:" & .UsedRange.Rows.Count).Delete
    End If
    Set Shtm = Workbooks("MasterData.xls").ActiveSheet
    For I = 4 To Shtm.UsedRange.Rows.Count
       If CLng(Shtm.Cells(I, 4).Value) = CLng(.Range("C2").Value) Then
          mRow = .UsedRange.Rows.Count + 1
          .Cells(mRow, 1).Value = Shtm.Cells(I, 1).Value
          .Cells(mRow, 2).Value = Shtm.Cells(I, 2).Value
          .Cells(mRow, 3).Value = Shtm.Cells(I, 3).Value
          .Cells(mRow, 4).Value = Shtm.Cells(I, 4).Value
          .Cells(mRow, 5).Value = Shtm.Cells(I, 5).Value
       End If
    Next
End With
End Sub

Open in new window

0
 
CartilloAuthor Commented:
Hi Wchh,

Thanks for the code. Have tested the code and it shows and error as “ script out of range” at this line:

Set Shtm = Workbooks("MasterData.xls").ActiveSheet

 I believe this is mainly because my MasterData workbook is resided in a network folder (\\dc04\data), is possible to locate this workbook in this folder?  
0
 
apresenceCommented:
Solution attached.  The relevant VBA code is in Sheet1 of the SearchData.xls file:
Option Explicit

Private m_oMasterDataSheet As Worksheet

'- 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

'- 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 lKey As Long
    
    '- Convert key to a number (Long).  This allows us to type "77777" as well as "077777" or "0077777", because they are all equivalent
    lKey = VarToLong(Key)
    If (lKey = -1) Then Exit Function
    
    nSrcRow = 4 '- Search data begins on row 4
    nDstRow = 5 '- Results begin on row 5
    Do
        '- Matching against column 4 (Box Number)
        vValue = m_oMasterDataSheet.Cells(nSrcRow, 4).Value
        
        '- First empty/blank value indicates end of source data
        If (IsEmpty(vValue)) Or (Len(vValue) = "") Then Exit Do
        
        If (VarToLong(vValue) = lKey) 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

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
apresenceCommented:
Forgot to attach the file... SearchData.xls
0
 
Saqib Husain, SyedEngineerCommented:
Try my version

Saqib
SearchData.xls
0
 
CartilloAuthor Commented:
Hi apresence,

Can you add other column as well besides column D, need to look for column A and E as well. One more thing, Search workbook is not sharing the same folder with MasterData.

Hi Saqib,

It shows an error at this line as "define object error"

Me.Worksheets(1).Range("C2").Validation.Add xlValidateList, , , vlist

Is that possible to set the Masterdata folder name: (\\dc04\data)
0
 
Saqib Husain, SyedEngineerCommented:
are you using the same files which were uploaded or a different file?
0
 
CartilloAuthor Commented:
Hi ssaqibh,

Using the same file that you've posted.
0
 
Saqib Husain, SyedEngineerCommented:
with the same master you posted?
0
 
CartilloAuthor Commented:
Hi ssaqibh,

Yes, you're right.
0
 
Saqib Husain, SyedEngineerCommented:
That beats me. It is working fine with me. Anyway that was an additional feature where I tried to include a dropdown list in C2 so that selection would be easy.

For the moment you can delete the macro in the workbook module and see if the macro in the worksheet module is good enough.

Saqib
0
 
CartilloAuthor Commented:


Hi ssaqibh,

The error happen after I open the workbook, when I click “End” the dropdown list disappear
0
 
Saqib Husain, SyedEngineerCommented:
I know. The dropdown is not working

Delete that workbook macro and save the file and reopen it and see if the other macro works
0
 
apresenceCommented:
Cartillo, I'd be happy to create a version that also searches on other fields if you post a new question.  I answered the question you asked.
0
 
Saqib Husain, SyedEngineerCommented:
My version will search on other fields as well with no change.
0
 
Saqib Husain, SyedEngineerCommented:
Cartillo, with the workbook macro in place try opening the master workbook first and then open the search workbook
0
 
CartilloAuthor Commented:
Hi apresence,

I posted new Q for extent the search function. Hope you can help me, attached the link:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26927593.html
0
 
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
 
CartilloAuthor Commented:
Hi ssaqibh,

I have deleted macro (Sub Workbook_Activate) and tested only with sheet 1 macro, however the error still persist at this line:

"Set mwb = Workbooks("masterdata").Worksheets("masterlist") as "subscript out of range"
0
 
CartilloAuthor Commented:
0
 
apresenceCommented:
I suggest you split the points for this one between myself and ssaqibh since we both worked to assist you with a solution.  Thanks!
0
 
CartilloAuthor Commented:
Hi apresence/Saqib,

Thanks for the great help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 8
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now