+ Reply to Thread
Results 1 to 9 of 9

Automate select, copy, paste command with VBA

  1. #1
    Registered User
    Join Date
    04-12-2021
    Location
    Japan
    MS-Off Ver
    XP
    Posts
    5

    Automate select, copy, paste command with VBA

    Hello Members and Experts,

    Cross-posted at https://www.mrexcel.com/board/thread...-loop.1167869/ (note that an answer has been posted there) --6SJ

    I need to efficiently record a list of 80 bowlers for further analysis and their scores and hope to obtain your help. The table only contains 4 columns:

    column A: Member's Number
    column B: Member's Name
    column C: Rank
    column D: Score

    The most headache is the fact that not all bowlers play exactly same number of game; some play more, some play less, so each member will occupy different number of rows for each score. Eg. the 1st bowler plays 4 games, 2nd bowler plays 6 games, and the 3rd bowler plays 3 games, and so forth. Thus column A and B, starting from row 2 for the first bowler, contain empty rows until the next bowler appears. I then have to copy each bowler's number (column A) and name (column B) and paste to every single row of game score.

    With my very limited knowledge on VBA/Macro I can only create below one that can accomplish the first bowler, but without being able to repeat the process to all others.


    Please Login or Register  to view this content.
    I realize some loop process with the likes of Do...Until, Do...While, For....Next but just don't know the appropriate syntax.

    Thanks very much in advance.
    Attached Files Attached Files
    Last edited by 6StringJazzer; 04-12-2021 at 11:51 AM. Reason: Moderator added code tags and cross-post link

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,421

    Re: Automate select, copy, paste command with VBA

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,844

    Re: Automate select, copy, paste command with VBA

    What analysis is required. Do you want to calculate the average of scores per match?
    Formula for average in E2 then copy down

    =IF($B2="","",AVERAGE($D2:INDEX($D3:$D$18,IFERROR(MATCH("*",$B3:$B$18,0)-1,ROW($B$18)-ROW()))))
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 04-12-2021 at 11:29 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,293

    Re: Automate select, copy, paste command with VBA

    Please Login or Register  to view this content.

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,740

    Re: Automate select, copy, paste command with VBA

    Because this is your first post, I suggest you take the time to review our rules. There aren't many, and they are all important.

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    I have added the link since this is your first post.

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I have added the code tags since this is your first post.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  6. #6
    Registered User
    Join Date
    04-12-2021
    Location
    Japan
    MS-Off Ver
    XP
    Posts
    5

    Re: Automate select, copy, paste command with VBA

    Hello Jeff,
    I am sorry for breaking rules, if any, as it's not my intention to do so. Certainly will pay attention to that in future.
    Also sorry for my ignorance, just don't understand the impact of posting same question in other forum.
    Anyway I will bear in mind.

    Cheers

  7. #7
    Registered User
    Join Date
    04-12-2021
    Location
    Japan
    MS-Off Ver
    XP
    Posts
    5

    Re: Automate select, copy, paste command with VBA

    Thank you so much to AlphaFrog, that works after adjusting "A1:B" to "A2:B".
    Last edited by canippon; 04-13-2021 at 12:43 AM.

  8. #8
    Registered User
    Join Date
    04-12-2021
    Location
    Japan
    MS-Off Ver
    XP
    Posts
    5

    Re: Automate select, copy, paste command with VBA

    To Torachan,
    thank you for your help anyway but that doesn't work.
    Last edited by canippon; 04-13-2021 at 12:44 AM.

  9. #9
    Registered User
    Join Date
    04-12-2021
    Location
    Japan
    MS-Off Ver
    XP
    Posts
    5

    Re: Automate select, copy, paste command with VBA

    Quote Originally Posted by kvsrinivasamurthy View Post
    What analysis is required. Do you want to calculate the average of scores per match?
    Formula for average in E2 then copy down

    =IF($B2="","",AVERAGE($D2:INDEX($D3:$D$18,IFERROR(MATCH("*",$B3:$B$18,0)-1,ROW($B$18)-ROW()))))

    It's more than just analyzing average, other factors like consistency as well trend are needed too.

+ 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. [SOLVED] Using VBA to automate copy/paste on new Sheet
    By mdolinger in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-12-2016, 09:18 AM
  2. How to automate copy paste
    By alyasch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2013, 09:36 AM
  3. Automate copy and paste and copy and paste back to excel
    By Bmw318be in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-23-2013, 05:42 AM
  4. Automate copy and paste and copy and paste back to excel
    By Bmw318be in forum Access Tables & Databases
    Replies: 0
    Last Post: 02-23-2013, 05:42 AM
  5. How do I select copy and paste using IF command?
    By Sanghvi in forum Excel General
    Replies: 1
    Last Post: 02-21-2009, 08:07 AM
  6. Automate copy command
    By cwilliams in forum Excel General
    Replies: 0
    Last Post: 04-05-2005, 02:06 PM
  7. Automate copy command?
    By cwilliams in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-05-2005, 02:06 PM

Tags for this Thread

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