start search from beginning with static variable

[danovkos]

hi all,
pls. i have two hard questions.

1)
How to change this code, that it will display msg „searching finished to end“ when it search all data till end. After OK it will search from beginning.
2) how to define, that the last part of this code (about pressing CTRL) will cleare all, as it is now, and also will clear variable, and it will start searching from beginning? Now it after clearing continue from previous position.
thank you very much for help

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 "Not found "
              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
      With TextBoxALL
          TextBoxALL.Value = ""
          ActiveSheet.TextBoxALL.Activate
          Range("j1").ClearContents
      End With
 
  End If
 
End Sub

[p45cal]

for part 2 of your question, add the line:
Set rCl = Nothing
to clear the static variable.
The next time it runs, the lines
If rCl Is Nothing Then
Set rCl = rSrch.Cells(1, 1)
will ensure it starts at the beginning again.
By the way, be aware that if there are multiple occurrences of what you're searching for in the range, and which one you find is important, then the bit of code 'After:=rCl' really means that, the cell rCl is the very last cell it will look in, because it starts searching from the cell after rCl.

By the way, in this code snippet, you don't need (nor use) the With construct:
[vba]If KeyCode = vbKeyControl Then
With TextBoxALL
TextBoxALL.Value = ""
ActiveSheet.TextBoxALL.Activate
Range("j1").ClearContents
Set rCl = Nothing
End With
End If[/vba]
[vba]If KeyCode = vbKeyControl Then
TextBoxALL.Value = ""
ActiveSheet.TextBoxALL.Activate
Range("j1").ClearContents
Set rCl = Nothing
End If
[/vba] As to part 1: what are you trying to do? I don't see a loop for the FindNext instruction so it will only find max of two instances of what you're looking for. It's too much work for me to try to reverse engineer this code, especially as it's not doing what you want it to do..

[danovkos]

ok,
i will try your codes tomorow, but i think, that this vs. (=nothing) i tried and it didnt works, but i will try again.

for part 1
this code works for textbox
this textbox works following:
- after press enter it search value from txtbox in data and result write to cell (J1)
- after enter again, it search the same value but after the last founded (it continue in searching from last position) and if it will found, it write it to the same cell (J1)

thank you for your help

[danovkos]

yes, this works great ...thank you

If KeyCode = vbKeyControl Then 
    TextBoxALL.Value = "" 
    ActiveSheet.TextBoxALL.Activate 
    Range("j1").ClearContents 
    Set rCl = Nothing 
End If 

and how can i do part 1?
maybe helps, when i use any rating of data in last column and then it will somewhere write line where starts and after using the same number...it appear msgbox...but how to do this?