PDA

View Full Version : start search from beginning with static variable



danovkos
10-15-2009, 04:04 AM
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
10-15-2009, 09:01 AM
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:
If KeyCode = vbKeyControl Then
With TextBoxALL
TextBoxALL.Value = ""
ActiveSheet.TextBoxALL.Activate
Range("j1").ClearContents
Set rCl = Nothing
End With
End If
If KeyCode = vbKeyControl Then
TextBoxALL.Value = ""
ActiveSheet.TextBoxALL.Activate
Range("j1").ClearContents
Set rCl = Nothing
End If
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
10-15-2009, 10:22 AM
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
10-15-2009, 11:40 PM
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?