G
Guest
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
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