Solved Alter macro created by DerbyDad03's

September 26, 2011 at 11:22:06
Specs: Windows XP, Intel Core 2/3.25GHz
Posting DerbyDad03 answered with a macro. I have been using that macro to lookup numbers in a worksheet, however some of the numbers have "XXXXX text". So for instance I place in sheet 3 the following:


but sheet 1 will list those number as such:

7688723 orlando
3647585 orlando

some of the numbers will have specific text like "orlando" after the number. When I run the marco, the search is returning on the rows for 7865768 and 4857362. How can the macro be altered so that the search will look for the numbers as well as any matching number that have text like "orlando" after the number. Here is the macro DerbyDad03 created:


1 - Sheet 1 Row 1 contains Column Headings.
2 - Sheet 2 is the destination sheet for the copied rows.
3 - You want the same Column Headings on Sheet 2 that are on Sheet 1.
4 - Sheet 3 Column A contains the list of Gene names to search for, starting in A2.

Before running the following code, place the list a Gene Names to be searched for in Sheet 3 Column A, starting in A2.

I suggest running the code in a backup copy of your workbook since macros can not be undone.

Option Explicit
Sub GeneFinder()
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
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
End With
End Sub

See More: Alter macro created by DerbyDad03s

Report •

September 26, 2011 at 17:01:10
✔ Best Answer

Try changing:


to be:


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

Report •

September 27, 2011 at 14:19:49
That rocks! Thank you sir/madam!

Report •

Related Solutions

Ask Question