PDA

View Full Version : why it find only each second value



danovkos
01-25-2010, 08:29 AM
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:


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

Bob Phillips
01-25-2010, 08:57 AM
Works fine in my test.

danovkos
01-26-2010, 03:48 AM
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
01-27-2010, 01:17 AM
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
01-27-2010, 02:12 AM
I thing, that i fixed it
i replace this line
Set rCl = rCl.Offset(1, 0)

with this line

Set rCl = rCl.Offset(0, 0)


i hope it will works always :)