+ Reply to Thread
Results 1 to 8 of 8

[SOLVED] Keyword search, copy rows to new sheet. Results far from consistent

  1. #1
    Registered User
    Join Date
    07-27-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    [SOLVED] Keyword search, copy rows to new sheet. Results far from consistent

    Hello,

    This is a piece of code from a question previously answered which is perfectly applicable to the worksheet attached.

    The problem lies in the fact that when certain strings are entered, whether individually or in a search for multiple strings, no results are given and a quick use of Ctrl F shows that this is not the case.

    Any ideas on fixing would be much appreciated!
    Attached Files Attached Files
    Last edited by Smatths; 08-01-2011 at 11:12 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Keyword search, copy rows to new sheet. Results far from consistent

    You still have error trapping on from early on in the code. If you switch it on after going through the find loop(s), ... (On Error Goto 0) ... you will find that you are getting an error 1004.

    Please Login or Register  to view this content.

    The error message says:

    "That command cannot be used on multiple selections"

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Keyword search, copy rows to new sheet. Results far from consistent

    That error message is puzzling since you CAN apply a "copy" on multiple ranges and paste them to a new sheet at a single point as shown that the macro usually works.

    Smatths, it is working for me, so you'll need to provide examples of those strings that cause problems when you search for them.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Keyword search, copy rows to new sheet. Results far from consistent

    I tried "oil" and "gas" and the range was built up in the loop.

    However, no rows were copied across.

    I then switched on the error trapping at the end of the loop and got the error message.

    If I search for, say, "crude", I only get one cell address and the macro works.

    If I search for "contractor", I get five cells and the macro *still* works. HWIT?

    ?findrng.Address
    $E$71,$A$75,$A$78,$E$82:$E$83,$A$84:$A$87,$A$89,$A$106,$E$106,$E$109,$A$528,$E$536,$A$571 mooring > fails
    $A$54,$E$54,$A$209,$A$218,$A$317,$A$331,$A$337 oil > fails
    $E$68:$E$69,$E$352,$E$518,$A$596 contractor > works
    $E$54 crude > works

    Edit: note - tested in Excel 2003 in Windows 7

  5. #5
    Registered User
    Join Date
    07-27-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Keyword search, copy rows to new sheet. Results far from consistent

    'riser' is a decent example given the multitude of occurences in the data

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Keyword search, copy rows to new sheet. Results far from consistent

    You know, I think I may see the problem. try editing this one line of code so the array built up is all in column A:

    Please Login or Register  to view this content.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Keyword search, copy rows to new sheet. Results far from consistent

    riser fails for me and generates the error message. 44 cells found in "Find All".

    ?findrng.Address
    $A$79,$E$89,$A$111:$A$119,$A$130,$A$143,$A$154,$E$220,$A$219:$A$222,$A$227,$E$227,$A$229:$A$230,$A$236:$A$237,$A$239:$A$240,$A$270,$A$300,$A$314,$A$319,$A$348:$A$349,$A$375,$A$386,$A$388,$E$392,$A$491,$A$530,$E$539,$E$544,$A$573,$A$576,$A$615

    Regards

  8. #8
    Registered User
    Join Date
    07-27-2011
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Keyword search, copy rows to new sheet. Results far from consistent

    Quote Originally Posted by JBeaucaire View Post
    You know, I think I may see the problem. try editing this one line of code so the array built up is all in column A:

    Please Login or Register  to view this content.
    Sneaky!

    Many thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1