Link to home
Start Free TrialLog in
Avatar of Cartillo
CartilloFlag for Malaysia

asked on

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
Avatar of wchh
wchh
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

Avatar of Cartillo
Cartillo
Flag of Malaysia image

ASKER

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?  
ASKER CERTIFIED SOLUTION
Avatar of apresence
apresence
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of apresence
apresence
Forgot to attach the file... SearchData.xls
SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cartillo
Cartillo
Flag of Malaysia image

ASKER

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)
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
are you using the same files which were uploaded or a different file?
Avatar of Cartillo
Cartillo
Flag of Malaysia image

ASKER

Hi ssaqibh,

Using the same file that you've posted.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
with the same master you posted?
Avatar of Cartillo
Cartillo
Flag of Malaysia image

ASKER

Hi ssaqibh,

Yes, you're right.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
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
Avatar of Cartillo
Cartillo
Flag of Malaysia image

ASKER



Hi ssaqibh,

The error happen after I open the workbook, when I click “End” the dropdown list disappear
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
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
Avatar of apresence
apresence
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.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
My version will search on other fields as well with no change.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
Cartillo, with the workbook macro in place try opening the master workbook first and then open the search workbook
Avatar of Cartillo
Cartillo
Flag of Malaysia image

ASKER

Hi apresence,

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

https://www.experts-exchange.com/questions/26927593/Add-additional-search-function.html
SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Cartillo
Cartillo
Flag of Malaysia image

ASKER

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"
Avatar of apresence
apresence
I suggest you split the points for this one between myself and ssaqibh since we both worked to assist you with a solution.  Thanks!
Avatar of Cartillo
Cartillo
Flag of Malaysia image

ASKER

Hi apresence/Saqib,

Thanks for the great help.