danovkos
09-30-2009, 05:58 AM
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
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