SOLVED: simultaneous searching

[danovkos]

Hi, all
please can you help me with this code? It search name in data range
in the same sheet during the writting.
It work perfect, but my data are 6000 row long and the searching takes a long time.
Please how can i:
- search in sheet1 in data which are in sheet2
- to search after a shor time (0,5 s) or after writting 3 character not immediately after 1. character

thank you very much for each help



Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Dim i As Long, j As String
Range("j2:j2").ClearContents
Rem Application.Wait waitTime
j = UCase(TextBox1.Value)
If TextBox1.Value = "" Then Exit Sub
For i = 69 To Range("A65536").End(xlUp).Row
If InStr(1, UCase(Cells(i, 1).Value), j) > 0 Then
Range("j2").End(xlUp).Offset(1, 0).Value = Cells(i, 1).Value
End If
Next i
End Sub

[Bob Phillips]

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Dim i As Long
Dim cell As Range
Range("j2:j2").ClearContents
Rem Application.Wait waitTime

If TextBox1.Value = "" Then Exit Sub

With Range(Range("A69"), Range("A65536").End(xlUp))

Set cell = .Find(What:=Cells(i, 1).Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not cell Is Nothing Then
firstcell = cell.Address
Do

Range("J2").End(xlUp).Offset(1, 0).Value = cell.Value
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> firstcell
End If
End With
End Sub

[danovkos]

Thx for help.
this code give me error after starting writting - Application defined or object defined error and show me this part in code


Set cell = .Find(What:=Cells(i, 1).Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)

whats wrong?

edit: i add atach.

[Bob Phillips]

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Dim i As Long
Dim cell As Range
Dim FirstCell As String
Range("J2").ClearContents
Rem Application.Wait waitTime

If TextBox1.Value = "" Then Exit Sub

With Worksheets("Sheet2")

With .Range(.Range("A1"), .Range("A65536").End(xlUp))

Set cell = .Find(What:=TextBox1.Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not cell Is Nothing Then
FirstCell = cell.Address
Do

Range("J2").End(xlUp).Offset(1, 0).Value = cell.Value
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> FirstCell
End If
End With
End With
End Sub

[danovkos]

yes this works great...
please and the last thing what i wrote in my first post..
now it seach immediately and i need it after 0,5 sec. or after 3 character..is this possible?
thanke you

[danovkos]

please can you help with the last question?
i tried search something what will help me, but i am beginer in vba and i dont know how? :(
i know, that i want very complicated code...but this is the end of my questions

can be this code helpfull to begin searching after written the 3th character (diggits) or what i need tu add to my code?




Do Until StrLen = DataLen

Pos = InStr(StrPos, Data, ",", vbTextCompare)
GtData = Mid(Data, 1, Pos + 1)
GtData = Trim(Replace(GtData, ", ", ""))
StrLen = Pos + 1
StrPos = Pos

MsgBox GtData

Loop


thank you very much

[mdmackillop]

Please have a little more patience. We are not employed here and have other duties/responsibilities like earning a living!
If you need an immediate response, you must be prepared to pay for it. Check out our Consulting services.

[danovkos]

i understand,
sorry for my impatience.
I know, that you do your best and for nothing (money) and i am very thankefull for this.

I will waiting.

thank you

[Bob Phillips]

Do you mean that instead of neing triggered by every keypress, you want it when the textbox is complete. How about having a button alongside to trigger the search, or double-click the textbox?

[danovkos]

i want to start search not after pressing any button or double click, but after writting the 3th character in textbox
exmple
if i write in textbox "a" it will not start to search. If i write "ab" - not start, but if i write "abc" it will start to search and after each next character it will be always search and return results as it works till now...
it is so possible?
thanke for your time

[Bob Phillips]

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Dim i As Long
Dim cell As Range
Dim FirstCell As String
Range("J2").ClearContents
Rem Application.Wait waitTime

If TextBox1.Value = "" Then Exit Sub

If Len(TextBox1.Text) < 3 Then Exit Sub

With Worksheets("Sheet2")

With .Range(.Range("A1"), .Range("A65536").End(xlUp))

Set cell = .Find(What:=TextBox1.Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not cell Is Nothing Then
FirstCell = cell.Address
Do

Range("J2").End(xlUp).Offset(1, 0).Value = cell.Value
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> FirstCell
End If
End With
End With
End Sub

[danovkos]

perfect,
thank you very very much

and one more time
sorry for my impatience

[mdmackillop]

danovkos
Please read this (http://www.excelguru.ca/node/7)

[danovkos]

plese can i ask one more quetion to this thread?
how can i extend my search on 3 range in 3 sheets together?

i mean the textbox will search not only in one search in one area but in 3
in
sheets1 area A1:A5000
sheets2 area B1:B5000
sheets3 area C1:C5000

THANK you

[Bob Phillips]

Just repeat the search code, changing the target areas.

[danovkos]

yes, i tried it but it search nothing

look

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Dim i As Long
Dim cell As Range
Dim FirstCell As String
Range("J2").ClearContents
Rem Application.Wait waitTime

If TextBox1.Value = "" Then Exit Sub

If Len(TextBox1.Text) < 3 Then Exit Sub

With Worksheets("2008")
With Worksheets("2007")
With .Range(.Range("e1"), .Range("e65536").End(xlUp))
With .Range(.Range("a1"), .Range("a65536").End(xlUp))

Set cell = .Find(What:=TextBox1.Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not cell Is Nothing Then
FirstCell = cell.Address
Do
Range("J2").End(xlUp).Offset(1, 0).Value = cell.Value
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> FirstCell
End If
End With
End With
End With
End With

[Bob Phillips]

No, sequentially



With Worksheets("2008")

With .Range(.Range("A1"), .Range("A65536").End(xlUp))

...
End With
End With

With Worksheets("2007")

With .Range(.Range("e1"), .Range("e65536").End(xlUp))

...
End With
End With

[danovkos]

i tried it after your post but this doesnt found nothing :(


Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Dim i As Long
Dim cell As Range
Dim FirstCell As String
Range("J2").ClearContents
Rem Application.Wait waitTime

If TextBox1.Value = "" Then Exit Sub
If Len(TextBox1.Text) < 3 Then Exit Sub

With Worksheets("2008")
With .Range(.Range("e1"), .Range("e65536").End(xlUp))

Set cell = .Find(What:=TextBox1.Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not cell Is Nothing Then
FirstCell = cell.Address
Do
Range("J2").End(xlUp).Offset(1, 0).Value = cell.Value
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> FirstCell
End If
End With
End With

With Worksheets("2007")
With .Range(.Range("a1"), .Range("a65536").End(xlUp))

Set cell = .Find(What:=TextBox1.Value, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not cell Is Nothing Then
FirstCell = cell.Address
Do
Range("J2").End(xlUp).Offset(1, 0).Value = cell.Value
Set cell = .FindNext(cell)
Loop While Not cell Is Nothing And cell.Address <> FirstCell
End If
End With
End With
End Sub

[danovkos]

now i modified my code and it works
thank you very much for you advise

[danovkos]

please one little thing. I hope.
How can i do this, that will works as it works plus it will be search also after press enter. Now it search after adding 3 character but if i sometimes need to search only ater 2 can i press enter and it starts to search? is any possibility to do this?
thx

[danovkos]

hi all,
please how can i change this code that it works like this:

- it will not search simultaneous but after "enter"
- and how to define for other button, that it will continue in search the next value if the first was not the right? (like in real find and "find next")

thank you very much

[mdmackillop]

Please post a workbook with code and data to test.

[danovkos]

hi,
here is my wb.

In real is bigger with more sheets and with simultanous search it takes a long time. This is reason, why i want to change it to searching with enter.
Ideal solution for me is, if it will search after pressing enter (it find the first value with this name) and after send enter again, it will search other value with the name and again and again...
Or other alternative, after enter find the first and there will be other button like (fine next).
thx for help

[danovkos]

this atach doesnt hlep to understund me?
i can try it explain again..
or what other can i do?
thank you

[danovkos]

hi,
pls. now i think i am near to my imagine.
this code works good, but not exactly as i wish.
now it search after starting writting to textbox. Is it very fast and
only thing what i miss to ideal is that after pressing "enter" will find the next value in column. After clicking out of text box it will clear textbox.
Pls. how can i do that?
thx a lot



Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i As Long, j As String
Range("C:C").ClearContents
Do
j = UCase(TextBox1.Value)
If TextBox1.Value = "" Then Exit Sub
For i = 1 To Range("A8000").End(xlUp).Row
If InStr(1, UCase(Cells(i, 1).Value), j) > 0 Then
Range("C2").End(xlUp).Offset(1, 0).Value = Cells(i, 1).Value
End If
Next i
' Me.TextBox1 = vbNullString

Exit Sub
Loop

End Sub

[danovkos]

hi,
i stoped this thread, because is here not easy to understund and maybe now i want something diferent as was at the begining.

I started new one here:
http://www.vbaexpress.com/forum/showthread.php?t=28617

[mdmackillop]

A slightly different approach.