+ Reply to Thread
Results 1 to 12 of 12

Find Text - Copy Range Below - Paste to Cell

  1. #1
    Forum Contributor
    Join Date
    08-28-2012
    Location
    nz
    MS-Off Ver
    Excel 2010
    Posts
    125

    Smile Find Text - Copy Range Below - Paste to Cell

    Hi

    1/ Need Macro to find text (Any Given) down a column

    2/ Copy the immediate range below Text down to 1st blank cell

    3/ Paste to given cell (Any Given), in this case S445


    Please see Wb for 2 easy Examples to work on

    Cheers Dudes


    Mike
    Attached Files Attached Files
    Last edited by keen2xl; 02-01-2014 at 06:17 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Find Text - Copy Range Below - Paste to Cell

    Hi Mike,

    so for one, in example 2 you are pasting in a different range, typo or intended??

    for Q2, you only seem to be interested in data from col F being copied and pasted, is this correct?

    how do you see the values to search for and which col to paste it into being determined.

    Just the first two "what is he actually doing" type questions.

    Last question, and no slight intended, what is your VBA macro experience level (helps to know when adding comments into a macro explaining what is being done)

    Cheers

    Jmac1947

    1. Please consider clicking on the * Add Reputation if you think this post has helped you
    2. Mark your thread as SOLVED when question is resolved

  3. #3
    Forum Contributor
    Join Date
    08-28-2012
    Location
    nz
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Find Text - Copy Range Below - Paste to Cell

    Hi

    Q: so for one, in example 2 you are pasting in a different range, typo or intended??

    Ans: Not typo , pasted into S445 in both sheets as shown and typed.

    Q: for Q2, you only seem to be interested in data from col F being copied and pasted, is
    this correct?

    Ans: Yes find L350 Column F in this case then copy all cells Below down to 1st variable blank cell / paste into S445 as shown in both sheets

    This is Web Extraction Data which will Vary

    Q: how do you see the values to search for and which col to paste it into being determined.

    Ans:
    Macro will search for Text, in this case L350 in Col F
    Macro will Copy all Cells Down to 1st Blank Cell (Variable)
    Macro will Paste those cells into S445


    That is the task at hand

    My experience is average copy from Web sites and adjust as needed or hit record and make, but this is a little bit more tricky.

    Thanks Cheers
    Last edited by keen2xl; 02-01-2014 at 05:43 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Find Text - Copy Range Below - Paste to Cell

    Hi Mike,
    slight cross purposes here, perhaps I didn't explain my questions carefully enough

    Q1. Yes I agree that they are both pasted into S445 however on tab 2 the actual data is starting around F448 (from memory). MY question was trying to get clear if S445 was a magic position or just Col S in the same row as the text was found? From your two examples I was / am not sure

    Q3. again, will the text to be searched for ALWAYS be "L350" or will you want to make it variable. If so will you be entering your search term into a cell in the worksheet, wanting a dialogue box to ask for the value etc.

    The task itself is quite easy, just don't want to suggest code that doesn't do what you are expecting

  5. #5
    Forum Contributor
    Join Date
    08-28-2012
    Location
    nz
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Find Text - Copy Range Below - Paste to Cell

    Hi ya

    see below

    Cheers
    Last edited by keen2xl; 02-01-2014 at 09:56 PM.

  6. #6
    Forum Contributor
    Join Date
    08-28-2012
    Location
    nz
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Find Text - Copy Range Below - Paste to Cell

    Hi ya

    All good understand : )

    Q1. Yes I agree that they are both pasted into S445 however on tab 2 the actual data is starting around F448 (from memory).

    Me: Starting positions will change up and down columns as it is a Web Pull from the net

    MY question was trying to get clear if S445 was a magic position

    Me: Yes S445 is majic position please always paste there : )

    Q3. again, will the text to be searched for ALWAYS be "L350" or will you want to make it variable.


    Me : Text will be Variable, I will change within your code and that will be that, will make multiple macro clones, exp : Macro Findtext1 / Findtext2 etc with different search criteria and i will change paste position for each one within the code also

    If so will you be entering your search term into a cell in the worksheet, wanting a dialogue box to ask for the value etc.

    Me : No i will just hard code within your code

    Yer so hope that helps i will call this macro from another running macro

    Cheers matey

  7. #7
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Find Text - Copy Range Below - Paste to Cell

    OK, I have finished the code, just one last tidy up question left.

    IF the output will always commence in s455 THEN the assumption is that there will only ever be one set of data in each input tab that has L350 in col F

    My instinct is that you are pulling form and applying the jockey & trainer due for a win type logic and that there will be multiple races results each with the same literal in each download

  8. #8
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Find Text - Copy Range Below - Paste to Cell

    Mike,

    take a look at the code in the attached workbook.

    There are actually two versions of the code, the comments tell you the differences

    You should be able to adapt one of both sets of code, if you dont need one of the solutions the simply delete from macro code

    Find Any Text _Copy Range Below_ Paste_jmac1947.xlsm

    Jmac1947

    1. Click on the * Add Reputation if you think this post has helped you
    2. Mark your thread as SOLVED when question is resolved
    Last edited by jmac1947; 02-01-2014 at 11:24 PM. Reason: add in signature

  9. #9
    Forum Contributor
    Join Date
    08-28-2012
    Location
    nz
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Find Text - Copy Range Below - Paste to Cell

    Hi

    Looking pretty good : )

    Just wondering is there any chance after the 1st instance of Text and Cells to copy have been found that, that is all it searches for and copies over to output Column ?

    Just realised some columns do have repeat instances Like "Horse" : )

    Just to keep things tidy in out put Column , ya know : )

    Hope that doesn't cause to much hassle.

    You Aussie Fellas aren't to bad after all

    Respect Bro

    Mike : )

  10. #10
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Find Text - Copy Range Below - Paste to Cell

    Choice

    (I lived on your side of the ditch for 4-5 years, used to have a good sample of K1W1 expressions to throw about, not to mention a passable accent )

    OK,

    so to be very clear, the one macro in this new workbook will only process ONE set of data after it finds the "Start marker" - hard coded to L350 at present.

    It will always start the output in S455 irrespective of where it first finds the "Start marker"

    Once it finds the first blank cell in Col F after it starts to copy it will end all processing except to save the workbook

    Think that about does it Bro, you can mark the thread solved (see thread tools in your first post) and if you think the result has helped you then consider clicking the "* Add Reputation" button on this post.

    Hope the horse system works for you, if you want to confide in the system then see my contact details in the macro code

    Find Any Text _Copy Range Below_ Paste_jmac1947.xlsm



    John Mac.

  11. #11
    Forum Contributor
    Join Date
    08-28-2012
    Location
    nz
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Find Text - Copy Range Below - Paste to Cell

    Very Good

    But you gona not like me

    Noticed macro finds

    TAB
    HORSE
    L50
    L350

    But Not

    Jockey
    Trainer

    ?????

    Can you remedy any ideas why ?

    Your names top of the list for system : ) if i ever get it ticking properly, very elusive this horse racing stuff sustainability is the key arh ! : )

    Cheers John

  12. #12
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Find Text - Copy Range Below - Paste to Cell

    Dude, you are killing me

    OK, that problem is probably because I convert the search term to uppercase so Jockey which i suspect you entered <> JOCKEY which is what the macro is looking to match.

    add the following line

    Please Login or Register  to view this content.
    immediately after the lines
    Please Login or Register  to view this content.
    and you should be OK.

    Just as an side, what data are you expecting in the "magic area" if you select Jockey as your search term?

    NOW... if you had been a bit more specific early on then I would have changed the coding to make it considerably easier to change the search term. You were pretty dogmatic about Col F where you didn't actually want only col F

    cest la vie

  13. #13
    Forum Contributor
    Join Date
    08-28-2012
    Location
    nz
    MS-Off Ver
    Excel 2010
    Posts
    125

    Re: Find Text - Copy Range Below - Paste to Cell

    Sweet

    Works perfectly finds everything which is good

    You are very

    Kind
    Patient and
    Tolerant


    To the max

    Thanks John

    You are a legend a VBA God
    Attached Files Attached Files
    Last edited by keen2xl; 02-02-2014 at 06:03 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to copy a range of cells, paste them and then find next empty cell
    By comp23 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2013, 03:34 PM
  2. [SOLVED] Find text in column A then copy the cell to the right and paste it in sheet2
    By Alring in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-22-2013, 02:11 AM
  3. macro to find text string in cell range and paste in other cell range
    By slearner1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2013, 11:15 AM
  4. Find the specific text and copy and paste in another cell only if true.
    By SHARIB in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2012, 03:14 AM
  5. Find Text, Select range below, copy, paste to new work sheet
    By kim5012 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2011, 07:10 PM

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.82748 seconds
  • Memory Usage 9,213KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (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
  • (2)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (13)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (2)postbit_attachment
  • (13)postbit_legacy
  • (13)postbit_onlinestatus
  • (13)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (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
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • 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_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1