Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Search code won't find partial matches for P.N.s
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 64
Default 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/

.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find partial match from column A,B and fill partial match in C? Tacrier Excel Discussion (Misc queries) 4 October 24th 08 11:24 PM
Search for a partial number das Excel Worksheet Functions 2 May 30th 08 07:31 PM
Partial string search w/o VBA? MJW[_2_] Excel Discussion (Misc queries) 6 October 22nd 07 08:16 PM
Autofiltering for partial matches PeterJordan Excel Discussion (Misc queries) 1 January 12th 06 09:30 PM
Partial search and replace? Jamie Furlong Excel Discussion (Misc queries) 2 August 28th 05 03:54 PM


All times are GMT +1. The time now is 06:04 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement