In the below code , a word apple has been written , in my sheet i cant define words, it consists of 1000 words and i cant put any specific word, is there any way of to put it across like
*Dim wordto find as string wordtofind=*" " , thanks On Monday, April 11, 2016, Ashish Koul <koul.ash...@gmail.com> wrote: > Sub sample() > 'https://msdn.microsoft.com/en-us/library/office/ff839746.aspx > Dim wordtofind As String > wordtofind = "apple" > > With Worksheets(1).UsedRange > Set c = .Find(wordtofind, LookIn:=xlValues) > If Not c Is Nothing Then > firstAddress = c.Address > Do > c.Interior.Color = vbYellow > Set c = .FindNext(c) > Loop While Not c Is Nothing And c.Address <> firstAddress > End If > End With > End Sub > > > On Mon, Apr 11, 2016 at 8:30 PM, Priyanka Jaswal < > priyankajaswal...@gmail.com > <javascript:_e(%7B%7D,'cvml','priyankajaswal...@gmail.com');>> wrote: > >> I want to highlight that particular word so for example if there is a >> word retail in multiple columns and rows it should be highlighted with >> yellow color everywhr , only that word not a cell row or column, thanks >> >> >> On Monday, April 11, 2016, Ashish Koul <koul.ash...@gmail.com >> <javascript:_e(%7B%7D,'cvml','koul.ash...@gmail.com');>> wrote: >> >>> after match wht u wanna do highlight cell ? >>> >>> On Sun, Apr 10, 2016 at 10:04 PM, Priya <priyankajaswal...@gmail.com> >>> wrote: >>> >>>> 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. >>>> >>> >>> >>> >>> -- >>> *Regards* >>> >>> *Ashish Koul* >>> >>> >>> *Visit* >>> http://www.excelvbamacros.in >>> Like Us on Facebook <https://www.facebook.com/excelvbacodes> >>> Join Us on Facebook <http://www.facebook.com/groups/163491717053198/> >>> >>> >>> P Before printing, think about the environment. >>> >>> >>> >>> -- >>> 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 a topic in the >>> Google Groups "MS EXCEL AND VBA MACROS" group. >>> To unsubscribe from this topic, visit >>> https://groups.google.com/d/topic/excel-macros/dam-mSWx45E/unsubscribe. >>> To unsubscribe from this group and all its topics, 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. >>> >> -- >> 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 >> <javascript:_e(%7B%7D,'cvml','excel-macros%2bunsubscr...@googlegroups.com');> >> . >> To post to this group, send email to excel-macros@googlegroups.com >> <javascript:_e(%7B%7D,'cvml','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. >> > > > > -- > *Regards* > > *Ashish Koul* > > > *Visit* > http://www.excelvbamacros.in > Like Us on Facebook <https://www.facebook.com/excelvbacodes> > Join Us on Facebook <http://www.facebook.com/groups/163491717053198/> > > > P Before printing, think about the environment. > > > > -- > 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 a topic in the > Google Groups "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/excel-macros/dam-mSWx45E/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > excel-macros+unsubscr...@googlegroups.com > <javascript:_e(%7B%7D,'cvml','excel-macros%2bunsubscr...@googlegroups.com');> > . > To post to this group, send email to excel-macros@googlegroups.com > <javascript:_e(%7B%7D,'cvml','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. > -- 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.
