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 (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 :doh: , 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.. : pray2:

[Bob Phillips]

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



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

[danovkos]

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

[Bob Phillips]

Maybe you need to test if there is any data



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

[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



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

[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.php?p=195902&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-programming/703959-search-next-value-with-textbox-in-other-sheet.html#post2183695

[mdmackillop]

I still think UserForm is simplest, but post a sample relative to your last code.
To what sheet does this line apply?

Set rSrch = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

[danovkos]

I still think UserForm is simplest, but post a sample relative to your last code.
To what sheet does this line apply?

Set rSrch = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))


i use this line only when i use textbox and data in the same sheet. But now when i need something diferent (working with more sheets), i dont know, if is this line neccessary. But i dont know, how to define range for searching in other sheet.

[mdmackillop]

You previous post appears to search Column E (previously column F), Can you post a revised sample containing your latest code

[danovkos]

this is my latest code, but it absolutely doesnt works...because it only my desperate try. Is in this atached file.



Option Explicit
Option Compare Text
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("data1")
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 "Not found"
Set rCl = Nothing
End If
End With
End With
End With
End If

With Worksheets("data2")
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 "Not found"
Set rCl = Nothing
End If
End With
End With
End With

End Sub

[mdmackillop]

No time just now, but did you try the Userform Code in Post 21

[danovkos]

No time just now, but did you try the Userform Code in Post 21

I tried your sample, but as i wrote...i want to use this code and change it for my needs, and i want to use vlookup and other formulas which i know...
With userform i dont know work as good as i wish. So to change code with userform for my needs is for me unreal.

If i good understand, to define what i want is so hard and it take so long time? because nobody helps me till now. Maybe my explanations are unclear...:(

[mdmackillop]

This is minor modification to the last post in your original question

[danovkos]

yes, yes :))
this works great....
at first time a wrote, that have bug...but i deleted it, because i undestund, how it works...
perfect a really thank you for your big help

fantastic!

[danovkos]

one more time


FANTASTIC:clap:

:friends:

:clap: :clap: :clap: :clap: :clap:

[danovkos]

one more question :(
now i figured out, that
- i write any text in textbox e.g. "dan", then press enter
- it search values after next enter
- but when i add any character after first or second enter (e.g - "y" now it is "dany", it continue with searching with the start value "dan". It works with this value and it doesnt look, what will you type new in textbox...

how can fix it? :dunno
i know, that this thread is solved, but this it to this thread, nothing new
thx

[mdmackillop]

Does the Reset button not clear this?

[danovkos]

yes,it did,
but my previous code (which worked only in one sheet) worked that i could change value in textbox also during the searching.
I mean, i type "dan", after first enter i see, that is not good word, so i type the other character e.g. "y" (dany)...and so..
but if it is very complicated nevermind...
if it is not very complicated it will be usefull for me ... :)
thank you

[mdmackillop]

Try adding a Change event run Reset if the textbox changes.

[danovkos]

thx for tip,
i tried add this code for change, but it doesnt works.



Private Sub TextBox1_Change()
Set d = Nothing
Set a = Nothing
Cnt = 0
End Sub


or it is neccessary to add to this code?


Private Sub TextBox1_KeyDown(ByVal KeyCode As SForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then FindNextItem TextBox1.Text
End Sub


there i tried something like this:



With TextBox1
If TextBox1.Change Then
Set d = Nothing
Set a = Nothing
Cnt = 0
End If
End With


but also doesnt works :(
how to define, that textbox was changed ?