Solved: search next value with textbox

[danovkos]

I have this code and all the day i try figured out, how to search next value in column.
i have cell wich will be contain the last found data. That is C2. Now i need to define, how will be the code search after data after last founded value.
My imagine:
- i write name in textbox
- after „enter“ in found the first value in DB (column A)
- after pressing „enter“ again it will be search the next value with name from textbox



Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If Me.TextBox1.Value = "" Then Exit Sub
If KeyCode = 13 Then
Dim rSrch As Range
Dim 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))
With rSrch
Set rCl = .Find(fnd, 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

End If

End With
End If
End Sub


thats all

[mikerickson]

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
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
With rSrch
Set rCL = .Item(.Cells.Count)
End With
End If
With rSrch
Set rCl = .Find(fnd, After:=rCL.Offset(1,0), 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

End If

End With
End If
End Sub

[danovkos]

this code return error
type mismatch
debug on this line:
Set rCl = .Find(fnd, After:=rCl.Offset(1, 0), LookIn:=xlValues)

[mikerickson]

I forgot that After must be in the range, try this change
'...
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)

Rem etc.

[danovkos]

yes this works GREATE :rotlaugh:
:clap:
THANK You very very much

[danovkos]

sorry,
one short question to this thread.
what if my range data are in diferent sheet
in sheet "Vyvoj" in column F
how define this? i had this but it was other methods and it doesnt works for your code :(


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 = 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


thanks one more time

[mikerickson]

I'm not sure what you mean by "range data in a different sheet"

If the range is fully qualified, there should be no problems.