Search Tool

[gti_jobert]

Hi all,

I have a large spreadsheet with cols A-G that contain data. I am tryin
to implement a search tool on the spreadsheet - I can enter a string an
then press cmdButton and then it will find it for me. This work usin
the following code;


Code
-------------------

Private Sub cmdSearchSheet_Click()

Dim searchString As String, i%

searchString = txtSearch.Text

Application.ScreenUpdating = False

Columns("A:G").Select
Selection.Find(What:=searchString, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select

Application.ScreenUpdating = True

ActiveCell.Select

End Sub

-------------------


My problem is - how do I carry on searching (Find Next) the strin
within these columns, so it searching for every instance of th
searchString and not just the first one..

TIA, any ideas appreciated

 

[gti_jobert]

Hello again,

I have now produced the following - it works but could possibly b
coded more efficiently;


Code
-------------------

Option Explicit
Dim counter As Long

-------------------



Code
-------------------

Private Sub cmdSearchSheet_Click()

Dim searchString As String, i%
counter = counter + 1
searchString = txtSearch.Text

Application.ScreenUpdating = False

Columns("A:G").Select

If counter > 1 Then
Selection.Find(What:=searchString, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
For i = 1 To counter
Selection.FindNext(After:=ActiveCell).Activate
Next i
Else
Selection.Find(What:=searchString, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
End If

Application.ScreenUpdating = True

ActiveCell.Select

End Sub

 

[gti_jobert]

This code is more efficient - works perfectly too;


Code:
--------------------

Private Sub cmdSearchSheet_Click()

Dim searchString As String, i%
counter = counter + 1
searchString = txtSearch.Text

Application.ScreenUpdating = False

Columns("A:G").Select

If counter = 1 Then
Selection.Find(What:=searchString, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Else
For i = 1 To counter
Selection.FindNext(After:=ActiveCell).Activate
Next i
End If

Application.ScreenUpdating = True

ActiveCell.Select

End Sub