Multiple Searches with Multiple Finds

Microsoft Office excel 2007
May 31, 2011 at 15:34:09
Specs: Windows XP
@DerbyDad03

I found the Sub that DerbyDad03 created called GeneFinder() and it works perfectly for finding one instance of the "gene names in sheet1" from the list in sheet3. However, if there is more than one instance of the gene names in sheet1 it will only find the first one and then move on to the next term from sheet3. I was wondering if there was anyway to do a "findAll" sort of function for the items in sheet3?

Here was the original code that DerbyDad03 posted :


Option Explicit
Sub GeneFinder()
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 D, copy it top the next row in Sheet2
With Sheets(1).Columns("D")
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("D" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
Next
End With
End Sub

Thanks in advance for any help with this!!

Ariel


See More: Multiple Searches with Multiple Finds

Report •


#1
May 31, 2011 at 20:56:49
If you'll click on the last line of this post, read the instructions found at that link, and then repost the code as instructed so that it retains it's formatting, I'll see what I can do to modify it.

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


Report •

#2
May 31, 2011 at 21:39:38
Ok Thanks for looking at it!

Option Explicit
Sub GeneFinder()
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 top 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
July 20, 2011 at 09:23:00
hi. has there been any progress on this, as i have the same requirement and am not great with introducing loops within a loop!!

Report •
Related Solutions


Ask Question