|
Search code won't find partial matches for P.N.s
Hello, thanks for you time! I have created a spred sheet with aprx. 1
sheets and need to make looking for specific part numbers use friendly. Say in the spread sheet there is this PN(330570056G) lets sa the user only has just a chunk of the PN say (0570056) although ther may be muliple matches how can I change the code to find the cells wit only searching for that chunk of the PN? Here is the code I am using it is not case sensitive and works for complete PNs. Sub Advanced_Find() Dim MyMax Dim MyMin Dim WhatFor Dim InWhat Dim InWhole Dim NotHere MyMax = Sheets("Advanced Find").Cells(7, 11) MyMin = Sheets("Advanced Find").Cells(6, 11) WhatFor = Sheets("Advanced Find").Cells(9, 3) If IsDate(WhatFor) Then InWhat = xlFormulas InWhole = xlWhole Else InWhat = xlValues InWhole = xlPart If IsNumeric(WhatFor) Then InWhat = xlFormulas InWhole = xlWhole End If If WhatFor = "" Then Sheets("Advanced Find").Select Range("C9").Select GoTo End_Here End If End If On Error GoTo NotFound ' Enable error-handling routine. Sheets("Advanced Find").Cells(10, 12) = ActiveSheet.Index Sheets("Advanced Find").Cells(10, 11) = ActiveCell.Row Sheets("Advanced Find").Cells(10, 10) = ActiveCell.Column Cells.Find(What:=WhatFor, After:=ActiveCell, LookIn:=InWhat, _ LookAt:=InWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False).Activate If ActiveCell.Row < Sheets("Advanced Find").Cells(10, 11) Then Sheets("Advanced Find").Cells(10, 12) = Sheets("Advance Find").Cells(10, 12) + 1 If Sheets("Advanced Find").Cells(10, 12) MyMax Then Sheets("Advanced Find").Cells(10, 12) = MyMin End If Sheets(Sheets("Advanced Find").Cells(10, 12)).Select Range("A1").Select Advanced_Find End If If ActiveCell.Row = Sheets("Advanced Find").Cells(10, 11) Then If ActiveCell.Column = Sheets("Advanced Find").Cells(10, 10 Then Sheets("Advanced Find").Cells(10, 12) = Sheets("Advance Find").Cells(10, 12) + 1 If Sheets("Advanced Find").Cells(10, 12) MyMax Then Sheets("Advanced Find").Cells(10, 12) = MyMin End If Sheets(Sheets("Advanced Find").Cells(10, 12)).Select Range("A1").Select Advanced_Find End If End If If ActiveCell.Row = Sheets("Advanced Find").Cells(10, 11) Then If ActiveCell.Column < Sheets("Advanced Find").Cells(10, 10 Then Sheets("Advanced Find").Cells(10, 12) = Sheets("Advance Find").Cells(10, 12) + 1 If Sheets("Advanced Find").Cells(10, 12) MyMax Then Sheets("Advanced Find").Cells(10, 12) = MyMin End If Sheets(Sheets("Advanced Find").Cells(10, 12)).Select Range("A1").Select Advanced_Find End If End If GoTo End_Here NotFound: Sheets("Advanced Find").Cells(10, 12) = Sheets("Advance Find").Cells(10, 12) + 1 Sheets("Advanced Find").Cells(10, 9) = Sheets("Advance Find").Cells(10, 9) + 1 If Sheets("Advanced Find").Cells(10, 12) MyMax Then Sheets("Advanced Find").Cells(10, 12) = MyMin End If If Sheets("Advanced Find").Cells(10, 9) MyMax Then Sheets("Advanced Find").Select Range("C9").Select NotHere = "Could not find " & WhatFor MsgBox NotHere GoTo End_Here End If Sheets(Sheets("Advanced Find").Cells(10, 12)).Select Range("A1").Select Advanced_Find End_He Sheets("Advanced Find").Cells(10, 9) = 0 End Su -- Message posted from http://www.ExcelForum.com |
Search code won't find partial matches for P.N.s
My immediate thought would be to stick wild cards onto
either end of your search string: WhatFor="*" & Sheets("Advanced Find").Cells(9, 3).Value_ & "*" Cheers, Pete -----Original Message----- Hello, thanks for you time! I have created a spred sheet with aprx. 10 sheets and need to make looking for specific part numbers user friendly. Say in the spread sheet there is this PN (330570056G) lets say the user only has just a chunk of the PN say (0570056) although there may be muliple matches how can I change the code to find the cells with only searching for that chunk of the PN? Here is the code I am using, it is not case sensitive and works for complete PNs. Sub Advanced_Find() Dim MyMax Dim MyMin Dim WhatFor Dim InWhat Dim InWhole Dim NotHere MyMax = Sheets("Advanced Find").Cells(7, 11) MyMin = Sheets("Advanced Find").Cells(6, 11) WhatFor = Sheets("Advanced Find").Cells(9, 3) If IsDate(WhatFor) Then InWhat = xlFormulas InWhole = xlWhole Else InWhat = xlValues InWhole = xlPart If IsNumeric(WhatFor) Then InWhat = xlFormulas InWhole = xlWhole End If If WhatFor = "" Then Sheets("Advanced Find").Select Range("C9").Select GoTo End_Here End If End If On Error GoTo NotFound ' Enable error-handling routine. Sheets("Advanced Find").Cells(10, 12) = ActiveSheet.Index Sheets("Advanced Find").Cells(10, 11) = ActiveCell.Row Sheets("Advanced Find").Cells(10, 10) = ActiveCell.Column Cells.Find(What:=WhatFor, After:=ActiveCell, LookIn:=InWhat, _ LookAt:=InWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext _ , MatchCase:=False).Activate If ActiveCell.Row < Sheets("Advanced Find").Cells(10, 11) Then Sheets("Advanced Find").Cells(10, 12) = Sheets("Advanced Find").Cells(10, 12) + 1 If Sheets("Advanced Find").Cells(10, 12) MyMax Then Sheets("Advanced Find").Cells(10, 12) = MyMin End If Sheets(Sheets("Advanced Find").Cells(10, 12)).Select Range("A1").Select Advanced_Find End If If ActiveCell.Row = Sheets("Advanced Find").Cells(10, 11) Then If ActiveCell.Column = Sheets("Advanced Find").Cells(10, 10) Then Sheets("Advanced Find").Cells(10, 12) = Sheets("Advanced Find").Cells(10, 12) + 1 If Sheets("Advanced Find").Cells(10, 12) MyMax Then Sheets("Advanced Find").Cells(10, 12) = MyMin End If Sheets(Sheets("Advanced Find").Cells(10, 12)).Select Range("A1").Select Advanced_Find End If End If If ActiveCell.Row = Sheets("Advanced Find").Cells(10, 11) Then If ActiveCell.Column < Sheets("Advanced Find").Cells(10, 10) Then Sheets("Advanced Find").Cells(10, 12) = Sheets("Advanced Find").Cells(10, 12) + 1 If Sheets("Advanced Find").Cells(10, 12) MyMax Then Sheets("Advanced Find").Cells(10, 12) = MyMin End If Sheets(Sheets("Advanced Find").Cells(10, 12)).Select Range("A1").Select Advanced_Find End If End If GoTo End_Here NotFound: Sheets("Advanced Find").Cells(10, 12) = Sheets("Advanced Find").Cells(10, 12) + 1 Sheets("Advanced Find").Cells(10, 9) = Sheets("Advanced Find").Cells(10, 9) + 1 If Sheets("Advanced Find").Cells(10, 12) MyMax Then Sheets("Advanced Find").Cells(10, 12) = MyMin End If If Sheets("Advanced Find").Cells(10, 9) MyMax Then Sheets("Advanced Find").Select Range("C9").Select NotHere = "Could not find " & WhatFor MsgBox NotHere GoTo End_Here End If Sheets(Sheets("Advanced Find").Cells(10, 12)).Select Range("A1").Select Advanced_Find End_He Sheets("Advanced Find").Cells(10, 9) = 0 End Sub --- Message posted from http://www.ExcelForum.com/ . |
| All times are GMT +1. The time now is 11:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com