+ Reply to Thread
Results 1 to 8 of 8

Search document for rows containing a specific word, cut/paste into new sheet

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    Coal Township
    MS-Off Ver
    Excel 2010
    Posts
    8

    Search document for rows containing a specific word, cut/paste into new sheet

    I am attempting to sort data on a very large report, but I am very inexperienced with the use of VBA.

    In sheet 1 of this report are several columns of data. What I am looking to do is get a macro that will search for a specific word in either column A OR column F, then cut/paste the entire row containing that word onto sheet 2 and remove the blank row.

    Things to consider:
    1. Columns A and F will contain other information than just the word I am looking for.
    2. There may be, at times, a blank cell in column F
    3. The report is almost 7000 rows.
    4. The word may appear in BOTH column A AND column F in some rows.
    5. The word may appear with both capital and lower-case letters

    For instance, I may want to take any row containing the word "diagnosis" and cut/paste that entire row onto sheet 2. The word "diagnosis" may appear in 100 different rows and may appear in the same row more than once. The word "diagnosis" may also appear as "Diagnosis" or "DIAGNOSIS".

    I'm not sure if I left anything out. I appreciate any help that anyone can provide. If there are any additional concerns, please let me know and I will respond ASAP. Thanks again.
    Last edited by mea02300; 07-08-2013 at 04:17 PM. Reason: Correct information

  2. #2
    Forum Contributor
    Join Date
    07-24-2012
    Location
    Brentwood, TN
    MS-Off Ver
    Excel 2007 at home/2010 at work
    Posts
    228

    Re: Search document for rows containing a specific word, cut/paste into new sheet

    Don't know if this will work, so save the file you already have under a new name to keep your old information safe. I also don't know if this will also grab the words that have capital letters instead.

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    07-24-2012
    Location
    Brentwood, TN
    MS-Off Ver
    Excel 2007 at home/2010 at work
    Posts
    228

    Re: Search document for rows containing a specific word, cut/paste into new sheet

    Then for column F just put "F1:F7000" instead of "A1:A7000" for the range

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Search document for rows containing a specific word, cut/paste into new sheet

    mea02300,

    Give this a try:
    Please Login or Register  to view this content.
    Last edited by tigeravatar; 07-08-2013 at 05:09 PM. Reason: Removed the msgbox from the code. Thanks for spotting that, stnkynts!
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Search document for rows containing a specific word, cut/paste into new sheet

    You didn't specify how you are going to assign the "word" so I assumed an input box.

    Try this:

    Please Login or Register  to view this content.
    Note: You might want to pull the Msgbox out of tigeravatar's code. It was there for testing purposes im sure.

  6. #6
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Search document for rows containing a specific word, cut/paste into new sheet

    Quote Originally Posted by stnkynts View Post
    Note: You might want to pull the Msgbox out of tigeravatar's code. It was there for testing purposes im sure.
    Thanks for spotting that! I forgot to delete it out. You are quite right, it was there for testing

  7. #7
    Registered User
    Join Date
    07-08-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Search document for rows containing a specific word, cut/paste into new sheet

    I jsut asked this in another thread pretty much. Not sure how to link it but look at my history.

  8. #8
    Registered User
    Join Date
    06-24-2013
    Location
    Coal Township
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Search document for rows containing a specific word, cut/paste into new sheet

    Thank you very much everyone. I removed the msgbox and the code worked perfectly. Not a single bug or issue to report. I really appreciate it. That was a huge help.

+ 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.55231 seconds
  • Memory Usage 9,034KB
  • Queries Executed 14 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (3)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (8)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (8)postbit_legacy
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (46):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1