why it find only each second value

[danovkos]

Hi all,
please can you help me with this code?
It search in my DB in column A based textbox value. If it find something, it return this value. But now i figured out, that if there are more values wich starting with the same value (e.g. John) then it retur after each ENTER only each second value not each, which is in DB.
e.g.
in textbox i write John and press enter
My DB contains values
John Doe
john black
john white
john purple
john lila
It find the first value "john Doe", that OK, after other enter it have to find john black, but it return "john white" (that wrong), after enter again it "return lila" (wrong again).
Please where is problem? can you help me?
thx a lot
here is code:
[vba]

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
Static por As String
Dim FirstAddress As String

meno = Range("j1").Value

fnd = Me.TextBoxALL.Value
Set rSrch = Range(Cells(30, 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, LookAt:=xlPart)

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

If KeyCode = vbKeyTab Then
With TextBoxALL
TextBoxALL.Value = ""
Call VlozCIFdoVIEW_Click
End With
End If
If KeyCode = vbKeyControl Then
TextBoxALL.Value = ""
ActiveSheet.TextBoxALL.Activate
Range("j1").ClearContents
Set rCl = Nothing
End If
'If ZMAZvALL_Click = True Then
' TextBoxALL.Value = ""
' ActiveSheet.TextBoxALL.Activate
' Range("j1").ClearContents
' Set rCl = Nothing
'End If
End Sub
[/vba]

[Bob Phillips]

Works fine in my test.

[danovkos]

can be the problem, that my table is huge?
it is starts on line 30 and ends on line 6000 and there are columns to W character (about 30 col. )
Maybe is problem in this?

[danovkos]

now i figured out, that this part of code:

  Set rCl = rSrch.Cells(1, 1)

set the next value (which is in this case value, which i want) and the next part of code search the next after the founded value and move result +1
this code

With rSrch
    Set rCl = .Find(fnd, After:=rCl, LookIn:=xlValues, LookAt:=xlPart)
 
            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

maybe is there problem only in case, when data of table are sorted alphabeticaly. So my sample was wrong and it should be like this:

john black
John Doe
john lila
john purple
john white

please can you help me?
thx a lot

[danovkos]

I thing, that i fixed it
i replace this line
[VBA] Set rCl = rCl.Offset(1, 0)
[/VBA]
with this line
[VBA]
Set rCl = rCl.Offset(0, 0)
[/VBA]

i hope it will works always