|Posting http://www.computing.net/answers/of... 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:
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.
Dim srchLen, gName, nxtRw As Integer
Dim g As Range
'Clear Sheet 2 and Copy Column Headings
'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
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)