Get rid of the loop

[VB]

For years we been running a spreadsheet with a VBA program that would
let me find all the names on in the spreadsheet that match a certain
criteria . Say all namesthat contain a "Ji". I did this by entering Ji
in textbox1 and then running the column through a do loop and checking
each cell and if it matchs feed the cell content to a list box

Dim i, j As Integer
If Len(TextBox1.Text) = 1 Then
TextBox1.Text = UCase(TextBox1.Text)
End If
For i = 1 To 65535
If (Worksheets(ActiveWorkbook.ActiveSheet.Name).Cells(i,
1).Text) = "" Then
LastCell = i
Exit For
End If
Next i
ListBox1.Clear
j = 0
For i = 3 To (LastCell - 1)
If
InStr(LCase(Worksheets(ActiveWorkbook.ActiveSheet.Name).Cells(i,
1).Text), LCase(TextBox1.Text)) > 0 Then
ListBox1.AddItem
Worksheets(ActiveWorkbook.ActiveSheet.Name).Cells(i, 1).Text
UseNameCellLocation(j, 0) =
Worksheets(ActiveWorkbook.ActiveSheet.Name).Cells(i, 1).Text
UseNameCellLocation(j, 1) = CStr(i)
j = j + 1
End If
Next i
If ListBox1.ListCount = 0 Then
Range("A" & CStr(LastCell)).Select
End If


The problem is as the list has gotten bigger the process has gotten
slower. Is there the equvilent of an SQL type command that would
speed things up?


I now I could shift this over to access but the people who use this
spreadsheet are only comfortable using Excel!!

 

[Rodrigo Ferreira]

Did you try to use "Find"?
Some thing like this:

If Len(TextBox1.Text) = 1 Then
TextBox1.Text = UCase(TextBox1.Text)
End If
ListBox1.Clear

Set sAux =
Worksheets(ActiveWorkbook.ActiveSheet.Name).Range("A:A").Find( _
What:=TextBox1.Text, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not sAux Is Nothing Then
AddAux = sAux.Address
Do While Len(Trim(AddAux)) > 0
ListBox1.AddItem
Worksheets(ActiveWorkbook.ActiveSheet.Name).Range(sAux.Address).Text
Set sAux = Range("A:A").FindNext '(After:=ActiveCell)
If AddAux <> sAux.Address Then
AddAux = sAux.Address
Else
Exit Do
End If
Loop
End If