Search Code Help

sixman

New Member
Joined
Jan 11, 2004
Messages
11
Dim cFound As Boolean
Dim intIndex As Integer
bFound = False
intIndex = 0
Do While Not cFound And intIndex < ListBox1.ListCount
If UCase(Left(ListBox1.List(intIndex), Len(tb7.Text))) = UCase(tb7.Text) Then
ListBox1.ListIndex = intIndex
cFound = True
End If
intIndex = intIndex + 1
Loop

The above code is a snippet that I have in my program. It works perfectly for what I need the only problem with it is the if statement. It is set up to search my listbox for a name and then highlight the name but it has no "safety" if the name isn't found (i.e. flashes an error). I tried to put an Else statement in the code so if a name wasn't found it would flash a message and it worked fine, but it kept flashing the message even if a name was found. I hope this isn't too confusing. Any help would be greatly appreciated.
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Not sure what you mean by 'flashes an error' - does the VB fall over if there's no match? If so use an error handler...
eg
Code:
...
On Error GoTo Problemo:
Do While Not cFound And intIndex < ListBox1.ListCount 
...

Exit Sub

Problemo:
MsgBox "Error"
Resume Next

End Sub

Incidentally should your bFound read as cFound? This could be why your else statement didn't work... ie

If cFound <> True then MsgBox "no match" but you would need to set cFound to be false at the beginning of the loop - your code sets bfound to be false....
 

sixman

New Member
Joined
Jan 11, 2004
Messages
11
I have to retract my last post. The my code is still not working. The problem is when the End User searches for a name that is not in the ListBox. What happens is that I get an error saying OverFlow. I have a MsgBox in the code that says "Client Not Found" to stop the program from flasing an "Overflow" error if a name isn't found, but the problem is that my program will flash my MsgBox ("Client Not Found") even if the name is in the ListBox.

Dim cFound As Boolean
Dim intIndex As Integer
cFound = False
intIndex = 0
Do While Not cFound And intIndex < ListBox1.ListCount
If UCase(Left(ListBox1.List(intIndex), Len(tb7.Text))) = UCase(tb7.Text) Then
ListBox1.ListIndex = intIndex
cFound = True
Else: MsgBox "Client Not Found!"
End If
intIndex = intIndex + 1
Loop
 

Watch MrExcel Video

Forum statistics

Threads
1,111,598
Messages
5,541,163
Members
410,543
Latest member
ExcelGlenn
Top