Solved: search next value with textbox in other sheet

[danovkos]

Hi,
because i am stupid i marked my previous thread about this problem as solved.
Now i hope, i have last qustion.
I tried to solve it by my self, but it works again only with first two founded values.
What is wrong please in my code?
I want only to search values from textbox in other sheet "vývoj" in column "F" and result return to cell J2. And after enter it will search othere values with name from textbox...and again and again.
Thanks again and thx for your big patience with me.


previous thread was this:
http://www.vbaexpress.com/forum/showthread.php?t=28617

code, what i knew to change is here but.

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
       Dim rSrch As Range
       Static rCl As Range
       Dim fnd As String
       Dim meno As String
       Dim FirstAddress As String
       meno = Range("J2").Value
       fnd = Me.TextBox1.Value
   Dim i As Long
'    Dim cell As Range
   Static cell As Range
   Dim FirstCell As String
 
 
'        If cell Is Nothing Then
'            With rSrch
'                Set cell = .Item(.Cells.Count)
'            End With
'        End If
 
If Me.TextBox1.Value = "" Then Exit Sub
If KeyCode = 13 Then
 
       With Worksheets("vývoj")
           With .Range(.Range("f1"), .Range("f65536").End(xlUp))
           Set rSrch = .Range(.Range("f1"), .Range("f65536").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
 
                       If cell.Value = meno Then
                            Set cell = .FindNext(cell)
'                            Set cell = .Find(fnd, After:=cell, LookIn:=xlValues)
                           Range("J2").End(xlUp).Offset(1, 0).Value = cell.Value
                       End If
                       Set cell = .FindNext(cell)
'                       Loop While Not cell Is Nothing And cell.Address <> FirstCell
                   End If
           End With
        End With
   End If
End Sub

[danovkos]

i tried it solve by my self , but still i don know, how to define the last founded value . And then starting searching from this place
Now i tried it with this code..but it alway return the same value:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Do
'Loop
    Dim meno As String
    Dim i As Long
    Dim cell As Range
    Dim FirstCell As String
    Dim lastValue As String
meno = Range("J2").Value
'lastValue = meno.Address
    If KeyCode = 13 Then
    If TextBox1.Value = "" Then Exit Sub
'   If Len(TextBox1.Text) < 4 Then Exit Sub
     
   With Worksheets("vývoj")
        With .Range(.Range("f1"), .Range("f65536").End(xlUp))
            Set cell = .Find(What:=TextBox1.Value, _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            MatchCase:=False)
            
            If Not cell Is Nothing Then
'                FirstCell = meno.Address
                FirstCell = cell.Address
                                If cell.Value = meno Then
                                Set cell = .FindNext(cell)
                                End If
'                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 If
End Sub

can anyone help me?
i know, that i got a big support and help..

[Bob Phillips]

Does this work? If not, tell us what is wrong with it in clear terms

[vba]

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim rSrch As Range
Static rCl As Range
Dim fnd As String
Dim meno As String
Dim FirstAddress As String
Dim i As Long
Static cell As Range
Dim FirstCell As String

meno = Range("J2").Value
fnd = Me.TextBox1.Value

If Me.TextBox1.Value = "" Then Exit Sub

If KeyCode = 13 Then

With Worksheets("vývoj")

Set rSrch = .Range(.Range("F1"), .Range("F" & .Rows.Count).End(xlUp))
With rSrch

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

FirstCell = cell.Address
Do
Range("J2").End(xlDown).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 If
End Sub
[/vba]

[danovkos]

this code return "runtime error 1004"
"application-defined or object-defined error"
and stops in debug on this line
[VBA]Range("J2").End(xlDown).Offset(1, 0).Value = cell.Value[/VBA]

[Bob Phillips]

Maybe you need to test if there is any data

[vba]

If Range("J1").Value = "" Then

Range("J1").Value = cell.Value
ElseIf Range("J2").Value = "" Then

Range("J2").Value = cell.Value
Else

Range("J2").End(xlDown).Offset(1, 0).Value = cell.Value
End If
[/vba]

[danovkos]

doesnt helps
the same error

i dont know if you know, but i have data where i search in sheet "vyvoj" and textbox for searching in sheet "view"..

[Bob Phillips]

Have you a workbook you can upload?

[danovkos]

yes, here it is

[Bob Phillips]

This works, but a filter would be more efficient

[vba]

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim rSrch As Range
Static rCl As Range
Dim fnd As String
Dim meno As String
Dim FirstAddress As String
Dim i As Long
Static cell As Range
Dim FirstCell As String

meno = Range("J2").Value
fnd = Me.TextBox1.Value

If Me.TextBox1.Value = "" Then Exit Sub

If KeyCode = 13 Then

With Worksheets("vývoj")

Set rSrch = .Range(.Range("F1"), .Range("F" & .Rows.Count).End(xlUp))
With rSrch

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

FirstCell = cell.Address
Do
If Range("J2").Value = "" Then

Range("J2").Value = cell.Value
ElseIf Range("J3").Value = "" Then

Range("J3").Value = cell.Value
Else

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

[danovkos]

It works, but not as i want
Maybe you was confused by my last code...
this code i want to use it only because it works between sheets.
But original code did something diferent - it return all founded values. But now i try to figured out, how it can works only after pressing enter. Not all founded values return/wite to column J.

I try to use it all after enter (similar as find with ctrl+f)
i write name to textbox after "enter" that found something it write it to J2 and stops. After pressing "enter" again, it write to J2 next value from sheet "vyvoj" column F.
Sorry that i confused u .
This was only my desperates try tu use functional code (wich works between sheets. (code working in one sheet i have, but i dont know to change it.

[Bob Phillips]

It returned them all for me. Do you just want the first found, and then the second found on next Enter? Bit of an odd equest if so.

[danovkos]

this is the code which works as i wish, but only in one sheet. But now i have data in sheet vyvoj and textbox in other sheet view

Option Explicit
Option Compare Text
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Do
'Loop
    If Me.TextBox1.Value = "" Then Exit Sub
    If KeyCode = 13 Then
        Dim rSrch As Range
        Static rCl As Range
        Dim fnd As String
        Dim meno As String
        Dim FirstAddress As String
        meno = Range("C1").Value
        fnd = Me.TextBox1.Value
        Set rSrch = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
       If rCl Is Nothing Then
    Set rCl = rSrch.Cells(1, 1)
Else
    Set rCl = rCl.Offset(1, 0)
    If Application.Intersect(rCl, rSrch) Is Nothing Then Set rCl = rSrch.Cells(1, 1)
End If
With rSrch
    Set rCl = .Find(fnd, After:=rCl, LookIn:=xlValues)
             
            If Not rCl Is Nothing Then
                FirstAddress = rCl.Address
                Cells(Rows.Count, 3).End(xlUp).Offset(0, 0).Value = rCl.Value
                If rCl.Value = meno Then
                    Set rCl = .FindNext(rCl)
                    Cells(Rows.Count, 3).End(xlUp).Offset(0, 0).Value = rCl.Value
                End If
                
                Else
                MsgBox "Nenašiel"
                Set rCl = Nothing
            
            End If
             
        End With
    End If
End Sub

[danovkos]

It returned them all for me. Do you just want the first found, and then the second found on next Enter? Bit of an odd equest if so.

yes, i want the first found after first enter, and second after next enter always in J2 and so ...
reason: my DB is huge and i have there a lot of function, buttons, sumproduct and it has about 30MB and is very slow. So i want not to wait to return all results. So i want to search only one value and mostly (if i type correct name) is it the first result. If it is not right value, it use "enter" a try next value.

[mdmackillop]

Did you try this solution?
http://vbaexpress.com/forum/showpost...2&postcount=27

[danovkos]

Yes, i tried it.
but i looking for something else.

This code retrun all values with name from textbox.But i need only first founded value after "enter" and after next "enter" other value and after next "enter" next value...and all it write to one cell.
I can not use this code, because i will implement it to my DB view, where i have a lot of information in one screen and there is no place to return more then one results of searching. So i can not click on result, that it write it to this cell as it is in this code.
But it works between 2 sheets and this is important for me.

[mdmackillop]

If more than one result, you could write the results to a Userform Listbox and click this to populate your cell.

[danovkos]

Yes, thats truth, but this solution is for me more complicatet. I want this resutls for other using. E.g. next to cell with result i will use vlookup to searching ID of name in my DB and many other thing, which i know to use. Working with UserForm is for me very complicatet in next using.
This is the reason, why is for me ideal to return result in one cell, and then i know what and how i will do next.
Is it too hard to define this because i don know?

[danovkos]

Hi all,
finaly i use this code. It Works only in active sheet where i have data/source but it Works.
I have only one short question to this:

How can i redefine/clear static varible in this code? I need it because after pressing „Clear“ button i want to start search from begining.

this is my textbox code

Private Sub TextBoxALL_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'Do
'Loop
 
Sheets("ALL").TextBoxALL.Activate
 If Me.TextBoxALL.Value = "" Then Exit Sub
   If KeyCode = 13 Then
     Dim rSrch As Range
     Static rCl As Range
     Dim fnd As String
     Dim meno As String
     Dim FirstAddress As String
     meno = Range("j1").Value
     fnd = Me.TextBoxALL.Value
 Set rSrch = Range(Cells(20, 1), Cells(Rows.Count, 2).End(xlUp))
    If rCl Is Nothing Then
 Set rCl = rSrch.Cells(1, 1)
Else
 Set rCl = rCl.Offset(1, 0)
 If Application.Intersect(rCl, rSrch) Is Nothing Then Set rCl = rSrch.Cells(1, 1)
End If
With rSrch
 Set rCl = .Find(fnd, After:=rCl, LookIn:=xlValues)
 
          If Not rCl Is Nothing Then
             FirstAddress = rCl.Address
             Cells(Rows.Count, 35).End(xlUp).Offset(0, -25).Value = rCl.Value
             If rCl.Value = meno Then
                 Set rCl = .FindNext(rCl)
                Cells(Rows.Count, 35).End(xlUp).Offset(0, -25).Value = rCl.Value
             End If
 
             Else
             MsgBox "Dlžník sa nenachádza v našom archíve (rok 2007 - súčasnosť) "
             Set rCl = Nothing
         End If
 
     End With
 
 With TextBoxALL
 .SelStart = 0
 .SelLength = 500 ''anything >= to len of text
 End With
 
 End If
End Sub 

this is my clear button code

Private Sub ZMAZvALL_Click()
 
TextBoxALL.Value = ""
Range("j1").ClearContents
ActiveSheet.TextBoxALL.Activate
Me.TextBoxALL = vbNullString
'ActiveSheet.Protect
End Sub

i tried it to add this in clear code but it doesnt works

Static rCl As Range
With rCl
rCl = Nothing
End With

thank you very much

[danovkos]

Hi,
now i know how to cleare static variable (google) and i try to search names from textbox in other sheet again. But i have problem with define range in other sheet .
I try this, but it doesnt works.
What about is CODE:
- textbox is in sheet "view" and search names in sheet "2008" after pressing ENTER
- data in sheet 2008 (starts in row 4, col E)
- if it match, it write to "view" in cell J2
how to fix it?
thx a lot

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim i As Long
'    Dim cell As Range
    Static cell As Range
    Dim FirstCell As String
    Dim rSrch As Range
    Static rCl As Range
    Dim fnd As String
    Dim meno As String
    Dim FirstAddress As String
 
If TextBox1.Value = "" Then Exit Sub
    If KeyCode = 13 Then
        meno = Range("j2").Value
        fnd = Me.TextBox1.Value
 
                With Worksheets("2008")
                    With .Range(.Range("e1"), .Range("e65536").End(xlUp))
'                    Set rSrch = Range.Worksheet("2008")(Range("e1"), Range("e65536").End(xlUp))
                    Set rSrch = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
                        If rCl Is Nothing Then
                        Set rCl = rSrch.Cells(1, 1)
                        Else
                        Set rCl = rCl.Offset(1, 0)
                            If Application.Intersect(rCl, rSrch) Is Nothing Then Set rCl = rSrch.Cells(1, 1)
                            End If
                                With rSrch
                                Set rCl = .Find(fnd, After:=rCl, LookIn:=xlValues)
                                    If Not rCl Is Nothing Then
                                    FirstAddress = rCl.Address
                                    Cells(Rows.Count, 3).End(xlUp).Offset(0, 0).Value = rCl.Value
                                                If rCl.Value = meno Then
                                                Set rCl = .FindNext(rCl)
                                                Cells(Rows.Count, 3).End(xlUp).Offset(0, 0).Value = rCl.Value
                                                End If
                                    Else
                                                MsgBox "Nenašiel"
                                                Set rCl = Nothing
                                    End If
                                End With
                    End With
                End With
    End If
end sub

[danovkos]

i try it to solve on other forum
http://www.excelforum.com/excel-prog...ml#post2183695