Hi Craig! You can actually use wildcard characters... Here's one way I
can think of.
In the previous code, replace the sub *FindAndCopy* with the new
*FindAndCopy* below and add the new function *MatchWhole*.


Code:
--------------------
'Main subroutine...
Sub FindAndCopy()
SearchStr = InputBox("Enter search string:", "Find")

If SearchStr = "" Then Exit Sub

ColName = GetColName(InputBox("Under what column would you want to search?", "Column Name/Number", "A"))

If ColName = "" Then Exit Sub

Dim SearchRange As Range
Set SearchRange = Worksheets(rtDataSheet).Range(ColName & "2:" & ColName & "65536") '2 - exclude 1st row

'The following codes are a modified version of the 'Find' method example from the VBA Help
Dim FoundVal As Range
Dim FirstAddress As String

With SearchRange
Set FoundVal = .Find(SearchStr, LookIn:=xlValues, LookAt:=xlPart)

If Not FoundVal Is Nothing Then
FirstAddress = FoundVal.Address

Do
If MatchWhole(SearchStr, FoundVal.Value) Then
CopyToResultSheet FoundVal.EntireRow
End If

Set FoundVal = .FindNext(FoundVal)
Loop While (Not FoundVal Is Nothing) And (FoundVal.Address < FirstAddress)
End If
End With
End Sub

'Returns True if SearchString matches a "whole" word in Val, that is,
'SearchString either fully matches Val, or SearchString is a substring
'in Val immediately preceded or followed by 0 or 1 non-alphanumeric character,
'then preceded or followed by any number of characters.
Function MatchWhole(ByVal SearchString As Variant, ByVal Val As Variant, Optional ByVal CaseSensitive As Boolean = False) As Boolean
Dim RegExp1 As String
Dim RegExp2 As String
Dim RegExp3 As String
Dim RegExp4 As String

If Not CaseSensitive Then
SearchString = UCase(SearchString)
Val = UCase(Val)
End If

'match whole word
RegExp1 = SearchString

'match starting, followed by non-alphanumeric character, followed by any character
' "andy" matches "andy abc"
' "andy" matches "andy-123"
' "andy" matches "andy,abc"
' "andy" matches "andy.123"
' "andy" does not match "t andy"
' "andy" does not match "andy123"
' "andy" does not match "andyt"
' "andy" does not match "candy"
' etc...
RegExp2 = SearchString & "[!a-zA-Z0-9]*"

'match ending, preceded by non-alphanumeric character, preceded by any character
' "andy" matches "abc andy"
' "andy" matches "123-andy"
' "andy" matches "abc,andy"
' "andy" matches "123.andy"
' "andy" does not match "andy t"
' "andy" does not match "123andy"
' "andy" does not match "andyt"
' "andy" does not match "candy"
' etc...
RegExp3 = "*[!a-zA-Z0-9]" & SearchString

'match starting, followed by non-alphanumeric character, followed by any character AND
'match ending, preceded by non-alphanumeric character, preceded by any character
' "andy" matches "abc andy-123"
' "andy" matches ".andy,"
' "andy" matches "abc,andy?"
' "andy" matches "123.andy "
' "andy" does not match "andy t"
' "andy" does not match "123andy"
' "andy" does not match "andyt"
' "andy" does not match "candy"
' etc...
RegExp4 = "*[!a-zA-Z0-9]" & SearchString & "[!a-zA-Z0-9]*"

If (Val Like RegExp1) Or _
(Val Like RegExp2) Or _
(Val Like RegExp3) Or _
(Val Like RegExp4) Then
MatchWhole = True
Else
MatchWhole = False
End If
End Function
--------------------


Hope this helps... :)


Craig Freeman Wrote:
Hey T-=AEex,

Really no delay at all...

The problem I was having with 'xlwhole' was that if there were two or
more whole words in the cell, the function would return no results,
even if there was a match with one of the whole words. So if I was
searching for 'apple', and 'candy apple' was in the cell, no match was
made. What I want to eliminate, is a search for 'andy' returning
'candy'.

Any ideas?

Cheers,
Craig


--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=401201