morning all,

I have a sub that searches a list box of values for matching words from a
text box. The problem is I only want to find whole words and it will
currently return results if the letters are part of a word (ie searching for
low will return airflow) and I cannot seam to fix the problem. Below is the
sub.

Public Sub PopulateList()

Dim myCell As Range
Dim myRng As Range
Dim FoundCell As Range
Dim FoundRng As Range
Dim FirstAddress As String


With Worksheets([mySheet])

MainTagForm!ColumnListBox.Clear

Set myRng = .Range([myColumn] & "2", .Cells(.Rows.Count,
[myColumn]).End(xlUp))
End With

If MainTagForm!SearchTextBox = "" Then
For Each myCell In myRng.Cells
If myCell.Value < 0 Then
MainTagForm!ColumnListBox.AddItem myCell.Value
End If
Next myCell
End If

If MainTagForm!SearchTextBox < "" Then
' 'finds first value
For Each myCell In myRng.Cells
If myCell.Value =
Cells.Find(what:=MainTagForm!SearchTextBox.Value, LookIn:=xlFormulas,
lookat:=xlPart, MatchCase:=False) Then
MainTagForm!ColumnListBox.AddItem myCell.Value
FirstAddress = myCell.Address
Set FoundCell = myCell
End If
Next myCell

With myRng

Set myCell = FoundCell
Set FoundRng = Nothing


Do
' finds the rest of the values
If FoundRng Is Nothing Then
Set FoundRng = myCell
Else
Set FoundRng = Union(FoundRng, myCell)
End If

Set myCell = .FindNext(after:=myCell)
MainTagForm!ColumnListBox.AddItem myCell.Value
If myCell.Address = FirstAddress Then Exit Do
Loop
End With
End If

'MsgBox [mySheet]
End Sub

Hi Jim,

have you tried:

LookAt:=xlWhole

Helmut Weber

I keep getting a run time error 9, object variable or with block variable
not set.


"Helmut Weber" wrote:

Hi Jim,

have you tried:

LookAt:=xlWhole

Helmut Weber