Solved Excel: search occurrences string, put result in other sheet

December 15, 2011 at 06:38:25
Specs: Windows 7
Hi
I would like to be able to search for a string (a term) in a particular column in an Excel sheet (with approximately 800000 rows).
The term may appear as part of a compound, for example, conductor, (electric) conductor, (electric) conduction, so my guess is that I should ideally decide on a common string that I search for, e.g. *conduct*.
I would like the search results to be listed in a separate sheet, with the same structure as the data (i.e. same column headings). The results should comprise the rows in which the term was found. I believe my problem is very similar to the problem you (DerbyDad03) fixed for EvaQ8. So … I adapted the code that your wrote for EvaQ8 (thanks) as follows.
Option Explicit
Sub TermFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
Sheets(2).Cells.ClearContents
Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column C, copy it to the next row in Sheet2
With Sheets(1).Columns("C")
For gName = 2 To srchLen
Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
If Not g Is Nothing Then
nxtRw = Sheets(2).Range("C" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
Next
End With
End Sub

I entered into sheet 3 my search string (I put “conductor” rather than making something more complex like *conduct*) and I know that Sheet1 contains 9 rows containing this term in column C. BUT, although the program runs, it only comes up with one occurrence from Sheet1 (the first instance of “conductor” that it comes across) and does not pick up the other 8 occurrences. Could you point out for me what I have done wrong please.
The Excel data sheet has a simple structure:
A: header = ref--> column with ids comprising either or numbers and hyphens as separators in the format ###-##-##, or an alphanumeric reference with spaces as separators
B: header = Language--> column which contains a two-letter language code, e.g. en
C: header = Term --> column with terms comprising alphanumeric characters, tags and entities (this column needs to be searched)
D: header = TermAttribute --> column with terms comprising alphanumeric characters, tags and entities
E: header = Synonym1--> column with terms comprising alphanumeric characters, tags and entities
F: header = Synonym1Attribute --> column with terms comprising alphanumeric characters, tags and entities
G: header = Synonym2 ---> column with terms comprising alphanumeric characters, tags and entities
H: header = Synonym2Attribute --> column with terms comprising alphanumeric characters, tags and entities
I: header = Synonym2 ---> column with terms comprising alphanumeric characters, tags and entities
J: header = Synonym2Attribute --> column with terms comprising alphanumeric characters, tags and entities
K: header = Definition --> column with terms comprising alphanumeric characters, tags and entities
L: header = Symbol ---> column with terms comprising alphanumeric characters, tags and entities
M: header = Publicationdate --> column consisting of dates in the format yyyy-mm

As you can probably tell I know nothing about VBA – I am a terminologist trying to find instances of where the same term has been defined different times with a view to harmonization. I came across your site when looking for tips. I hope that I have the right to ask this question on your forum - if not, please forgive me. Any help that you could give me would be most gratefully received.
Thanks a lot.


See More: Excel: search occurrences string, put result in other sheet

Report •


#1
December 15, 2011 at 09:24:10
Please click on the following line and then repost your code so that the indents are retained.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 16, 2011 at 00:22:59
Dear DerbyDad03

Sorry to be a dummy - I did notice that you'd pointed that out in a previous reply and hadn't realized the significance. Here is the code. Thanks a lot for your help - I really appreciate it.

Option Explicit
Sub TermFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
 Sheets(2).Cells.ClearContents
 Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
   srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column C, copy it to the next row in Sheet2
  With Sheets(1).Columns("C")
    For gName = 2 To srchLen
      Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
        If Not g Is Nothing Then
          nxtRw = Sheets(2).Range("C" & Rows.Count).End(xlUp).Row + 1
          g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
        End If
    Next
  End With
End Sub


Report •

#3
December 16, 2011 at 05:36:30
✔ Best Answer
Here is the example for .Find from the Excel 2003 Help files. For some reason they no longer include this excellent example in the Help files for the later versions of Excel.

Note the inclusion of FindNext within the Do loop.

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 19, 2011 at 05:08:27
Thanks for the info. I think I understood something but I do not understand the the "c.Value=5" (as I said, I'm a terminologist and so not too hot with this sort of thing). So I tried omitting this line, and then putting "g.value = 1" and then "g.value = 20000" and still get no results. I clearly have misunderstood something. In the code below I've simply put "g.value = ?" since I am lost.

If you could help that would be great. If not, I'll understand that you have better things to do. Whatever, thanks again. I do appreciate your help and expertise.

Option Explicit
Sub TermFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
 Sheets(2).Cells.ClearContents
 Sheets(1).Rows(1).Copy Destination:=Sheets(2).Rows(1)
'Determine length of Search Column from Sheet3
   srchLen = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'Loop through list in Sheet3, Column A. As each value is
'found in Sheet1, Column C, copy it to the next row in Sheet2
  With Sheets(1).Columns("C")
    For gName = 2 To srchLen
      Set g = .Find(Sheets(3).Range("A" & gName), lookat:=xlWhole)
        If Not g Is Nothing Then
          nxtRw = Sheets(2).Range("C" & Rows.Count).End(xlUp).Row + 1
          g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
	  Do
            g.value = ?
            Set g = .FindNext(g)
          Loop While Not g Is Nothing And gName <> srchLen
        End If
    Next
  End With
End Sub


Report •

#5
December 19, 2011 at 06:46:06
If you are still having problems, what did you mark the thread as solved?

The c.value = 5 is simpy the action that the author wants to take each time it finds search string (2) in the example.

The example shows that if you want to repeat the desired action for every occurrence of whatever it is that you are searching for, you need to put those actions inside the Do Loop.

As I said in my earlier post: Note the inclusion of FindNext within the Do loop. That Do Loop is where you put the actions that you want the code to take as it finds each occurrence of the search string.

The modifications that you made to the example will cause it not to work as expected.

Let me try to break it down via comments:


'Set the range that you want to search
 With Worksheets(1).Range("a1:a500")
'Set up a variable to store the information associated with 
'the search criteria. In this case the search criteria is 2 
   Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
'If the value is found, store the address of the
'first found value in a variable called firstAddress
        firstAddress = c.Address
'Start the Do Loop
        Do
'Change the 2 to a 5
            c.Value = 5
'Find the next 2
            Set c = .FindNext(c)
'If another 2 is found and we haven't looped around to the first 
'2 that was found, perform the action (c.value = 5) again.
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

The reason we need to check for the address of the found value is because in cases like yours, you are not changing that value, you are just copying it. If we didn't check to see if we've looped back to the first occurrence of the search criteria, the code would just keep on looping and copying forever.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 4, 2012 at 12:05:13
Thanks for the extra info. With the help of a friend who is more IT savvy than I, everything now works. And so, problem solved.

By the way, I didn't intend to mark the thread as solved before. As a terminologist I do not consider "Best answer" and "Problem solved" to mean the same thing. I was trying to recognize that the answer that you had given me was the best one received - which it was since it helped me to advance! Now I can say "Definitive answer" or "Problem solved" but there is nothing to click on that says that.

Thanks again.


Report •


Ask Question