Search code won't find partial matches for P.N.s

[bmw34]

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_Here:
Sheets("Advanced Find").Cells(10, 9) = 0
End Su

 

[Pete McCosh]

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