Hi All,
Background:
I have a database (glorified list) in Excel that contains 8 Fields. The first field of each row contains part numbers separated by a comma and a space. Other characteristics specific to the part number or part numbers such as tool manufacturer and tool type appear in the other fields for each row. This worksheet is linked to a master workbook (different file), so that it updates automatically. This worksheet will available to manufacturing and will be read-only. The links for this worksheet were created by selecting each source column in the master, copying the entire column and then paste special: Link (this causes all cells with no value in the master to have a value of 0). This was done so that whenever a new row in the master is created, the links in this dependent workbook would not need to be updated. (If there is a better way to achieve this let me know)
Goal: I am trying to make this dependent workbook really easy to use for the folks in manufacturing that will need to search it, so I am trying to designate one cell or text box at the top as a search field. I will then have a button that runs the custom search macro to find all the matches to the one "search field" cell. I understand that the general response to this will be "Why not use Cntrl F (Excel's built-in functionality) or add a Find button to call Excel's built-in search dialog)", however this is not an option for the powers that be. I have read thread707-1318170.
Desired Behavior: When a search is performed I would like all rows that DO NOT MATCH the search criteria to be hidden with the exception of rows 1-3 which house the title, column headings and search cell, so that the only data rows that are visible are the ones that contain a match. (Alternatively, if all data rows were initially hidden, the search would make visible only the rows that contain a match)
What I've Tried: I've tried recording Find, using the Find and Find Next VBA scripts and I have tried using some code from thread707-1318170. I have also tried initially hiding all rows (4-655360) and then performing the search to unhide the matches (the problem with this is that Excel will not search hidden cell Values). Because this is a linked file and I have multiple part numbers in cells I realize I need to use the Find Option: Look in: Values.
Here's some of the code that I have tried, just to try and get closer to a solution, In this code I actually Hide the Results that Match, but this takes forever:
Sub SearchTest()
Dim X As Range
Set X = Cells.Find(What:=Range("B2"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Range("B2").Value = 0 Then
MsgBox ("Must Enter A Contact Number, Tool Number, Tool Type, etc... 0 is not specific enough")
Else
While Not X Is Nothing
X.EntireRow.Hidden = True
Set X = Cells.Find(What:=Range("B2"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
Wend
Range("A1").EntireRow.Hidden = False
Range("A2").EntireRow.Hidden = False
Range("A3").EntireRow.Hidden = False
MsgBox "No More Found"
End If
End Sub
Thanks for any advice you may have!!!! I seem to have searched as much as I can and fiddled around and still can't seem to get it right so I figured it was time to talk you, the great experts of VBA! Thanks again!
Background:
I have a database (glorified list) in Excel that contains 8 Fields. The first field of each row contains part numbers separated by a comma and a space. Other characteristics specific to the part number or part numbers such as tool manufacturer and tool type appear in the other fields for each row. This worksheet is linked to a master workbook (different file), so that it updates automatically. This worksheet will available to manufacturing and will be read-only. The links for this worksheet were created by selecting each source column in the master, copying the entire column and then paste special: Link (this causes all cells with no value in the master to have a value of 0). This was done so that whenever a new row in the master is created, the links in this dependent workbook would not need to be updated. (If there is a better way to achieve this let me know)
Goal: I am trying to make this dependent workbook really easy to use for the folks in manufacturing that will need to search it, so I am trying to designate one cell or text box at the top as a search field. I will then have a button that runs the custom search macro to find all the matches to the one "search field" cell. I understand that the general response to this will be "Why not use Cntrl F (Excel's built-in functionality) or add a Find button to call Excel's built-in search dialog)", however this is not an option for the powers that be. I have read thread707-1318170.
Desired Behavior: When a search is performed I would like all rows that DO NOT MATCH the search criteria to be hidden with the exception of rows 1-3 which house the title, column headings and search cell, so that the only data rows that are visible are the ones that contain a match. (Alternatively, if all data rows were initially hidden, the search would make visible only the rows that contain a match)
What I've Tried: I've tried recording Find, using the Find and Find Next VBA scripts and I have tried using some code from thread707-1318170. I have also tried initially hiding all rows (4-655360) and then performing the search to unhide the matches (the problem with this is that Excel will not search hidden cell Values). Because this is a linked file and I have multiple part numbers in cells I realize I need to use the Find Option: Look in: Values.
Here's some of the code that I have tried, just to try and get closer to a solution, In this code I actually Hide the Results that Match, but this takes forever:
Sub SearchTest()
Dim X As Range
Set X = Cells.Find(What:=Range("B2"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Range("B2").Value = 0 Then
MsgBox ("Must Enter A Contact Number, Tool Number, Tool Type, etc... 0 is not specific enough")
Else
While Not X Is Nothing
X.EntireRow.Hidden = True
Set X = Cells.Find(What:=Range("B2"), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
Wend
Range("A1").EntireRow.Hidden = False
Range("A2").EntireRow.Hidden = False
Range("A3").EntireRow.Hidden = False
MsgBox "No More Found"
End If
End Sub
Thanks for any advice you may have!!!! I seem to have searched as much as I can and fiddled around and still can't seem to get it right so I figured it was time to talk you, the great experts of VBA! Thanks again!