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/

.