Excel Search & display query
Closed
Shiny
-
Apr 16, 2010 at 12:10 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 8, 2011 at 02:23 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 8, 2011 at 02:23 PM
Related:
- Excel Search & display query
- Www.google.com search video - Download - Video playback
- Yahoo search history - Guide
- Excel date format dd.mm.yyyy - Guide
- Youtube search within channel - Guide
- Safe search settings - Guide
5 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 16, 2010 at 05:32 PM
Apr 16, 2010 at 05:32 PM
Assumptions:
1. Name of the sheet where the data is "Sheet1" (correct the code if its not)
2. Name of the sheet where the search result are to be pasted is "Result" (correct the code if its not)
3. Previous search results are to be discarded
4. Data is on 4 columns ( as in sample)
STEPS:
1. Read Assumptions
2. Make a back up
3. Press ALT + F11 at the same time to enter VBE environment
4. Click on "Insert" and add a new module
5. Paste the code ( after the instructions)
6. Run the code
Code:
1. Name of the sheet where the data is "Sheet1" (correct the code if its not)
2. Name of the sheet where the search result are to be pasted is "Result" (correct the code if its not)
3. Previous search results are to be discarded
4. Data is on 4 columns ( as in sample)
STEPS:
1. Read Assumptions
2. Make a back up
3. Press ALT + F11 at the same time to enter VBE environment
4. Click on "Insert" and add a new module
5. Paste the code ( after the instructions)
6. Run the code
Code:
Sub SearchData()
Dim lMaxRows As Long 'max number of rows of data based on cells used on column A
Dim lFilterRows As Long 'last filtered row
Dim searchRel As Variant 'what is to be search for Release Info
Dim searchProj As Variant 'what is to be search for Project Info
Dim searchPpl As Variant 'what is to be search for Contact Info
Dim sDataSheet As String 'name of the data sheet
Dim sResultSheet As String 'name of the result sheet
sDataSheet = "Sheet1" 'name of the data sheet
sResultSheet = "Result" 'name of the result sheet
'getting search criteria
searchRel = InputBox("What Release you want to search. To skip, just press OK.")
searchProj = InputBox("What Project you want to search. To skip, just press OK.")
searchPpl = InputBox("Which contact person you want to search. To skip, just press OK.")
'remove white spaces
searchRel = Trim(searchRel)
searchProj = Trim(searchProj)
searchPpl = Trim(searchPpl)
' if all three search criteria are blank then dont do any thing
If (Len(searchRel & searchProj & searchPpl) = 0) Then Exit Sub
On Error Resume Next
Application.DisplayAlerts = False
'delete previous result sheet if it exists
Sheets(sResultSheet).Delete
Application.DisplayAlerts = True
On Error GoTo 0
'adding the result sheet
Sheets.Add
ActiveSheet.Name = sResultSheet
Sheets(sDataSheet).Select
Cells.Select
'removing any filter
If ActiveSheet.AutoFilterMode Then
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
End If
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
If ActiveSheet.AutoFilterMode = False Then
Selection.AutoFilter
End If
If (searchRel) <> "" Then
Selection.AutoFilter Field:=2, Criteria1:="=" & searchRel, Operator:=xlAnd, Criteria2:="<>"
End If
If (searchProj) <> "" Then
Selection.AutoFilter Field:=3, Criteria1:="=" & searchProj, Operator:=xlAnd, Criteria2:="<>"
End If
If (searchPpl) <> "" Then
Selection.AutoFilter Field:=4, Criteria1:="=*" & searchPpl & "*", Operator:=xlAnd, Criteria2:="<>"
End If
lFilterRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:D" & lFilterRows).Copy
Sheets(sResultSheet).Select
Range("A1").Select
ActiveSheet.Paste
Sheets(sDataSheet).Select
Cells.Select
'removing any filter
If ActiveSheet.AutoFilterMode Then
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
End If
End Sub
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 16, 2010 at 02:07 PM
Apr 16, 2010 at 02:07 PM
How you foresee that you are searching ? Would you be using the excel filters?
Hi.. When I try to use the excel filters it is not working good for this requirement, since I have many conatct perosn asociated with particular project and these person were also involved in different projects... the filter in excel is not showing all the conatct perosn names for the project.Can you suggest me what should I do.. I dont know any language to code this..Pl help me on this...
Thanks a lotttttt
Thanks a lotttttt
Hi ,
Thanks for the Program... But I have one issue on that.. The seacrh result is diaplyaing all the items in the Result page.. instead of required items...
Thanks again a lot for the effort you have taken for me..
Thanks for the Program... But I have one issue on that.. The seacrh result is diaplyaing all the items in the Result page.. instead of required items...
Thanks again a lot for the effort you have taken for me..
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 19, 2010 at 09:32 AM
Apr 19, 2010 at 09:32 AM
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Hi,
I uploaded a test data file in the below mentioned link...
https://authentification.site/files/22032655/Test_File.xls
Thanks again for helping me with this...
I uploaded a test data file in the below mentioned link...
https://authentification.site/files/22032655/Test_File.xls
Thanks again for helping me with this...
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 19, 2010 at 10:18 PM
Apr 19, 2010 at 10:18 PM
Ok, my code had too error as result of copy and paste on my part. I have updated the code up there. These are updated lines
If (searchProj) <> "" Then
Selection.AutoFilter Field:=3, Criteria1:="=" & searchProj, Operator:=xlAnd, Criteria2:="<>"
End If
If (searchPpl) <> "" Then
Selection.AutoFilter Field:=4, Criteria1:="=*" & searchPpl & "*", Operator:=xlAnd, Criteria2:="<>"
End If
However, there is one issue. You have in example above, names separated by comma. How ever in the sample sheet, each name is in individual column, Which one is correct ? It initial was correct, this code should work. If each name is in separate cell, then code needs modification
If (searchProj) <> "" Then
Selection.AutoFilter Field:=3, Criteria1:="=" & searchProj, Operator:=xlAnd, Criteria2:="<>"
End If
If (searchPpl) <> "" Then
Selection.AutoFilter Field:=4, Criteria1:="=*" & searchPpl & "*", Operator:=xlAnd, Criteria2:="<>"
End If
However, there is one issue. You have in example above, names separated by comma. How ever in the sample sheet, each name is in individual column, Which one is correct ? It initial was correct, this code should work. If each name is in separate cell, then code needs modification
Didn't find the answer you are looking for?
Ask a question
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 20, 2010 at 10:27 AM
Apr 20, 2010 at 10:27 AM
Assumptions:
1. Name of the sheet where the data is "Sheet1" (correct the code if its not)
2. Name of the sheet where the search result are to be pasted is "Result" (correct the code if its not)
3. Previous search results are to be discarded
4. Data to be filtered exists in column B, C and From E and onward
5. Names to be search can be in any column from E and onward
STEPS:
1. Read Assumptions
2. Make a back up
3. Press ALT + F11 at the same time to enter VBE environment
4. Click on "Insert" and add a new module
5. Paste the code ( after the instructions)
6. Run the code
Code:
1. Name of the sheet where the data is "Sheet1" (correct the code if its not)
2. Name of the sheet where the search result are to be pasted is "Result" (correct the code if its not)
3. Previous search results are to be discarded
4. Data to be filtered exists in column B, C and From E and onward
5. Names to be search can be in any column from E and onward
STEPS:
1. Read Assumptions
2. Make a back up
3. Press ALT + F11 at the same time to enter VBE environment
4. Click on "Insert" and add a new module
5. Paste the code ( after the instructions)
6. Run the code
Code:
Sub SearchData()
Dim lMaxRows As Long 'max number of rows of data based on cells used on column A
Dim lFilterRows As Long 'last filtered row
Dim searchRel As Variant 'what is to be search for Release Info
Dim searchProj As Variant 'what is to be search for Project Info
Dim searchPpl As Variant 'what is to be search for Contact Info
Dim sDataSheet As String 'name of the data sheet
Dim sResultSheet As String 'name of the result sheet
Dim iMaxCol As Integer 'last column header
Dim iCount As Integer ' last count
Dim sFormula As String ' string for temp formula
sDataSheet = "Sheet1" 'name of the data sheet
sResultSheet = "Result" 'name of the result sheet
'getting search criteria
searchRel = InputBox("What Release you want to search. To skip, just press OK.")
searchProj = InputBox("What Project you want to search. To skip, just press OK.")
searchPpl = InputBox("Which contact person you want to search. To skip, just press OK.")
'remove white spaces
searchRel = Trim(searchRel)
searchProj = Trim(searchProj)
searchPpl = Trim(searchPpl)
' if all three search criteria are blank then dont do any thing
If (Len(searchRel & searchProj & searchPpl) = 0) Then Exit Sub
On Error Resume Next
Application.DisplayAlerts = False
'delete previous result sheet if it exists
Sheets(sResultSheet).Delete
Application.DisplayAlerts = True
On Error GoTo 0
'adding the result sheet
Sheets.Add
ActiveSheet.Name = sResultSheet
Sheets(sDataSheet).Select
Cells.Select
'removing any filter
If ActiveSheet.AutoFilterMode Then
On Error Resume Next
ActiveSheet.ShowAllData
ActiveSheet.AutoFilterMode = False
On Error GoTo 0
End If
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
iMaxCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
For iCount = 5 To iMaxCol
sFormula = sFormula & " & RC" & iCount & " & ""|"""
Next
If (sFormula <> "") Then sFormula = """|""" & sFormula
Range(Cells(1, iMaxCol), Cells(lMaxRows, iMaxCol)).FormulaR1C1 = "=" & sFormula
If ActiveSheet.AutoFilterMode = False Then
Selection.AutoFilter
End If
If (searchPpl) <> "" Then
Selection.AutoFilter Field:=iMaxCol, Criteria1:="=*|" & searchPpl & "|*", Operator:=xlAnd, Criteria2:="<>"
End If
If (searchRel) <> "" Then
Selection.AutoFilter Field:=2, Criteria1:="=" & searchRel, Operator:=xlAnd, Criteria2:="<>"
End If
If (searchProj) <> "" Then
Selection.AutoFilter Field:=3, Criteria1:="=" & searchProj, Operator:=xlAnd, Criteria2:="<>"
End If
lFilterRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:D" & lFilterRows).Copy
Sheets(sResultSheet).Select
Range("A1").Select
ActiveSheet.Paste
Sheets(sDataSheet).Select
Cells.Select
'removing any filter
If ActiveSheet.AutoFilterMode Then
On Error Resume Next
ActiveSheet.ShowAllData
ActiveSheet.AutoFilterMode = False
On Error GoTo 0
End If
Range(Cells(1, iMaxCol), Cells(lMaxRows, iMaxCol)).Clear
Range("A2").Select
End Sub
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 8, 2011 at 02:23 PM
Feb 8, 2011 at 02:23 PM
Rahul, you would have to loop thru each sheet to get the results
basically, from this point
Sheets(sDataSheet).Select
Cells.Select
you have to repeat for each sheet
if you are not strong on code side, then most easy way would be to have the result for each sheet go to a separate sheet and then have a code to combine the results into one master sheet.
basically, from this point
Sheets(sDataSheet).Select
Cells.Select
you have to repeat for each sheet
if you are not strong on code side, then most easy way would be to have the result for each sheet go to a separate sheet and then have a code to combine the results into one master sheet.
