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


[vba]
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

[/vba]

[Bob Phillips]

[vba]

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
[/vba]

[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]

[vba]

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
[/vba]

[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]

[vba]

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
[/vba]

[danovkos]

perfect,
thank you very very much

and one more time
sorry for my impatience

[mdmackillop]

danovkos
Please read this

[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

[vba]

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
[/vba]

[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