Hi,
I need help making a couple of changes in a code for a search button in vba.
These are the basic features in this search function:
- there are two buttons, a search button and a clear button to remove the highlight from the highlighted cells found in the previous search;
- the text to search is typed in cell B2;
- it only searches column D;
- after the text to search is typed, it only works by pressing the find button;
- it tells the number of matches found in cell C2.
This is the actual code I have:
It works perfectly. Two buttons, one for searching and another to clear. But I wan't to make a couple of changes in it, which are:
1- When there are no searching matches, no window pops-up like it does currently, it just makes a sound (or even nothing) and puts a zero (0) in C2 like it is currently doing (for faster user interface since there will be a lot of consecutive searching using the worksheet);
2- I need the Clear button to work like a reset search button also. I need that when this clear button is clicked, the screen goes back to the beginning of my worksheet, in my case to cell A4, and if possible it also deletes the text typed in B2 and resets the count in C2 to zero (0).
Maybe small changes but I don't have the knowledge or skills to be able to do it. The worksheet is attached to this post.
So can someone help me with it? Thank you very much in advance!
I need help making a couple of changes in a code for a search button in vba.
These are the basic features in this search function:
- there are two buttons, a search button and a clear button to remove the highlight from the highlighted cells found in the previous search;
- the text to search is typed in cell B2;
- it only searches column D;
- after the text to search is typed, it only works by pressing the find button;
- it tells the number of matches found in cell C2.
This is the actual code I have:
Code:
Sub tester()
Dim c1 As Range 'start cell...
Dim r1 As Long 'row found...
Dim r_old As Long 'prior row found...
Dim i As Integer 'for loop...
Dim count As Integer 'number of occurences...
Dim str As String 'string for message box...
Set c1 = Range("D1") 'Start search at this cell...
r1 = 1
r_old = 0
counter = 0
Columns("D:D").Interior.Pattern = xlNone 'Clear all YELLOW cells..
If Not ActiveCell.Column = 4 Then c1.Activate 'If not in Column D... then start from top
'###### Loop Column D for term...
For i = 1 To 5
On Error GoTo NoneFound 'If none found... go to sub procedure
r1 = Columns(4).Find(What:=Cells(2, 2).Value, After:=c1, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
On Error GoTo 0 'Default error handling...
Set c1 = Cells(r1, 4) 'Change start cell for Find function...
If r1 > r_old Then
counter = counter + 1 'Number of occurences...
str = str & vbCrLf & "Row " & r1
Cells(r1, 4).Interior.Color = 65535
Else
Exit For
End If
r_old = r1
Next i
Cells(2, 3).Value = counter
'MsgBox counter & " occurences of " & Chr(34) & Cells(2, 2).Value & Chr(34) & " found in:" & vbCrLf & str
Call Sub1
Exit Sub
'##### If none found... then this Sub procedures is run...
NoneFound:
Cells(2, 3).Value = 0
MsgBox "No occurences of " & Chr(34) & Cells(2, 2).Value & Chr(34) & " found"
End Sub
'###### Activates next found cell...
Sub Sub1()
Columns(4).Find(What:=Cells(2, 2).Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
End Sub
'###### Clears all YELLOW cells...
Sub Clear()
Columns("D:D").Interior.Pattern = xlNone
End SubIt works perfectly. Two buttons, one for searching and another to clear. But I wan't to make a couple of changes in it, which are:
1- When there are no searching matches, no window pops-up like it does currently, it just makes a sound (or even nothing) and puts a zero (0) in C2 like it is currently doing (for faster user interface since there will be a lot of consecutive searching using the worksheet);
2- I need the Clear button to work like a reset search button also. I need that when this clear button is clicked, the screen goes back to the beginning of my worksheet, in my case to cell A4, and if possible it also deletes the text typed in B2 and resets the count in C2 to zero (0).
Maybe small changes but I don't have the knowledge or skills to be able to do it. The worksheet is attached to this post.
So can someone help me with it? Thank you very much in advance!
