Excel macro only finds first entry

July 28, 2011 at 06:50:35
Specs: Windows XP
Sheet1 contains columns of data, Sheet2 is blank, and Sheet3 has my list of search terms in column A. I have a macro that will use the search terms from Sheet3, search Sheet1, column E (where the data are I need to search), and copy/paste that row onto Sheet2. My problem is that it only finds the first entry. I have two entries for each search term. Column E will show that it is a duplicate, but the data in a different column will be different. Is there a way to modify the macro I have, or do I need a different one? I'm a novice at best when it comes to macros. Any help would be appreciated.

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 both values are
'found in Sheet1, Column E, copy it top the next row in Sheet2
With Sheets(1).Columns("E")
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("E" & Rows.Count).End(xlUp).Row + 1
g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
End If
Next
End With
End Sub


See More: Excel macro only finds first entry

Report •


#1
July 28, 2011 at 07:38:10
This is from the Excel 2003 VBA help files for .Find, showing how to use .FindNext.

Unfortunately, the 2007+ versions of the Help files do not include this example.

Example
This example finds all cells in the range A1:A500 on worksheet one that 
contain the value 2 and changes it to 5.

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 •

#2
July 28, 2011 at 07:51:18
I don't quite understand how this will help me. Can you explain?

Report •

#3
July 28, 2011 at 11:48:12
You said:

"My problem is that it only finds the first entry. I have two entries for each search term."

Your original code only finds the first entry because it only searches the range once.

Once it Finds (or doesn't find) the value it is looking for [Range("A" & gName)], it simply continues through the For-Next loop, incrementing the search value row (gName) and searching the range for the new value.

What you need to do is have VBA search for the next occurance (.FindNext) of the value once it Finds it.

Since the Find function starts its search at the current cell location, searches downward and then loops back up to the top of the range and continues searching from there, the example code I posted shows that VBA stores the FirstAddress where Find found the value and continues to search for the same value until it finds it in the same address as it found the first occurance. That tells VBA that it has searched the entire range and found all occurances of the values, whether there was 1 or 100.

Inside the Do Loop is where you want to put the actions you want to occur each time the value is found, so that those actions are taken each time the value is found.

Something like:

  If Not g Is Nothing Then
        firstAddress = g.Address
        Do
           nxtRw = Sheets(2).Range("E" & Rows.Count).End(xlUp).Row + 1
           g.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtRw)
        Loop While Not g Is Nothing And g.Address <> firstAddress
    End If

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


Report •
Related Solutions


Ask Question