banshee001
New Member
- Joined
- Sep 28, 2022
- Messages
- 1
- Office Version
- 2021
- Platform
- Windows
Hello,
I have a search function that im using to list items in the listbox based on whats entered in the textbox, but as of now its only working with numbers.
the problem i had was that it wont search part of words or number, such as if the number was "12345" and i entered "345" in the textbox it wont list it. But for words/terms/characters im getting an error "type mismatch".
I tried different approaches but it wont search for parts of the words (using .textlength previously instead of .text)
the code is as follows:
Also i have another problem which is i get duplicates of the items when i search, for example if i search "345" i get three results of the same item.
thanks in advance
I have a search function that im using to list items in the listbox based on whats entered in the textbox, but as of now its only working with numbers.
the problem i had was that it wont search part of words or number, such as if the number was "12345" and i entered "345" in the textbox it wont list it. But for words/terms/characters im getting an error "type mismatch".
I tried different approaches but it wont search for parts of the words (using .textlength previously instead of .text)
the code is as follows:
VBA Code:
Sub search_all()
userform1.textbox1=lcase(userform1.textbox1)
dim sh as worksheet
set sh=sheet1
dim i as long
dim x as long
dim p as long, k as integer
with userform1.listbox1
userform1.listbox1.rowsource=""
.columnhead=false
.columncount=30
.columnwidth="........................"
.list=sh.range("A1", "AE1").value
for i = 2 to sh.range("B" & Rows.Count).end(xlup).row
for x = 1 to len(sh.cells(i,1))
for k = 1 to 30
p=userform1.textbox1.text
if Lcase(mid(sh.cells(i,k),x,p)) like "*" & userform1.textbox1.text & "*" then
.add item
.list (userform1.listbox1.listcount -1,0)=sh.cells(i,1)
..............
...........
.....
End if
next k
next x
next i
end with
end sub
Also i have another problem which is i get duplicates of the items when i search, for example if i search "345" i get three results of the same item.
thanks in advance