Hi
I am writing a code to look for same word in the worksheets , where ever it
is written in the worksheet, (basically need all the rows where that
particular word is written ). The data is huge and I cant set a code
specific to a word. The issue I am getting is that I only get one row from
all the results, whereas there are multiple rows and columns which includes
that particular keyword. Could you help.
Private Sub CommandButton1_Click()
Dim MyNewValue As Variant
Dim FoundCell As Object
Dim Counter As Long
Dim Flag As Boolean
Dim SearchString As Variant
'-------------------------------------------------
'- Initialise Components
Flag = False
Worksheets("Sector Results").Rows("2:" & Rows.Count).ClearContents
Worksheets("Function Results").Rows("2:" & Rows.Count).ClearContents
Worksheets("Sector Results").Visible = False
Worksheets("Function Results").Visible = False
'-------------------------------------------------
'- SET SEARCH KEY
SearchString = TextBox2.Value()
If SearchString = "" Then End
Counter = 0
'------------------------------------------------
'- FIND ALL MATCHING CELLS IN SECTOR KEYWORD SHEET
On Error Resume Next
Set ws = Worksheets("Sector Keywords").Range("B:E")
Set FoundCell = ws.Cells.Find(what:=SearchString,
SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.EntireRow.Copy Destination:=Sheets("Sector
Results").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'--------------------------------------------
Set FoundCell = ws.Cells.Find(what:=SearchString,
After:=FoundCell, SearchOrder:=xlByRows, SearchDirection:=xlNext)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
Worksheets("Sector Results").Visible = True
Flag = True
End If
'------------------------------------------------
'- FIND ALL MATCHING CELLS IN FUNCTION KEYWORD SHEET
Set ws = Worksheets("Function Keywords").Range("B:E")
Set FoundCell = ws.Cells.Find(what:=SearchString)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
Counter = Counter + 1
'--------------------------------------------
'- what to do if found
FoundCell.EntireRow.Copy Destination:=Sheets("Function
Results").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'--------------------------------------------
Set FoundCell = ws.Cells.FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
Worksheets("Function Results").Visible = True
Worksheets("Function Results").Activate
Flag = True
End If
If Flag Then
MsgBox ("Result Found")
Else
MsgBox ("No Result Found")
End If
End Sub
Private Sub TextBox2_Change()
Worksheets("Sector Results").Visible = False
Worksheets("Function Results").Visible = False
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
