+ Reply to Thread
Results 1 to 14 of 14

Adding If / Then Logic to a Copy / Paste VBA Macro

  1. #1
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Adding If / Then Logic to a Copy / Paste VBA Macro

    Hey all,

    I am playing with the following macro:

    Please Login or Register  to view this content.
    Nothing too crazy. It copies the comments in cells A1:A6 and pastes them into B1:B6. Taking the below scenerio, however:

    A1: Gold
    A2: Silver
    A3: Platinum
    A4: Diamond
    A5: Bronze
    A6: Rock

    B1: Silver
    B2: Platinum
    B3: Rock

    How can I make it so, rather than just duplicating the comments in A1:A6 and placing them into B1:B6, it will check to see what items are in B1:B6 and only give the respective comment from cells where a1 = b1, a2 = b2, etc. Example:

    A1: Gold (Comment: "Yellow")
    A2: Silver (Comment: "Silver")
    A3: Platinum (Comment: "Hard")
    A4: Diamond (Comment: "Pretty")
    A5: Bronze (Comment: "Orange")
    A6: Rock (Comment: "Brown")

    After macro...

    B1: Silver (Comment: "Silver")
    B2: Platinum (Comment: "Hard")
    B3: Rock (Comment: "Brown")
    B4: "" (no comment)
    B5: "" (no comment)
    B6: "" (no comment)

    Hopefully this makes sense! Any thoughts?
    Last edited by levitt; 08-06-2014 at 12:11 AM.

  2. #2
    Forum Contributor lancer102rus's Avatar
    Join Date
    04-02-2014
    Location
    Ufa
    MS-Off Ver
    Excel 2010, 2013
    Posts
    252

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    need this?
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    Quote Originally Posted by lancer102rus View Post
    need this?
    Hey there!

    That's very close, but there are a couple issues I'm running into.

    Some of the comments I have aren't just text - they've got images or textures. See the example I'll upload below:

    Example3.xlsm

    One of the comments is a texture, the other is a stock excel 2013 image. For the ones that are images, they aren't copying over when I hit your macro. I included the original macro that I was playing with to copy over all comments and a VERY sloppy delete all comments in B1:B6 (couldn't get anything else to work...) so if you happen upon a better way to do that as well, that would be amazing. If you toy with it, you'll see that the one which copied over all the comments did move the images, but the one you suggested didn't quite work like that.

    Any thoughts?
    Last edited by levitt; 08-06-2014 at 12:12 AM.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    Hi, levitt,

    Your opening post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    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, it also maintains VBA formatting.

    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


    Please have a look at Forum Rule #12 about quoting full posts as well.


    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    Quote Originally Posted by HaHoBe View Post
    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Should be back in compliance now, thanks for the heads up

  6. #6
    Forum Contributor lancer102rus's Avatar
    Join Date
    04-02-2014
    Location
    Ufa
    MS-Off Ver
    Excel 2010, 2013
    Posts
    252

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    Quote Originally Posted by levitt View Post
    Hey there!
    ...
    Any thoughts?
    just small change:
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor lancer102rus's Avatar
    Join Date
    04-02-2014
    Location
    Ufa
    MS-Off Ver
    Excel 2010, 2013
    Posts
    252

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    and delete comments:
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    Quote Originally Posted by lancer102rus View Post
    just small change:
    Ah, that's absolutely perfect!!! This is the last piece of a much larger project that you just helped me wrap up

    While I've got you, any thoughts on the comment delete button? I was currently using:

    Please Login or Register  to view this content.
    Because putting in "B1:B6" wasn't seeming to work. What am I doing wrong?

  9. #9
    Forum Contributor lancer102rus's Avatar
    Join Date
    04-02-2014
    Location
    Ufa
    MS-Off Ver
    Excel 2010, 2013
    Posts
    252

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    I`ve already answered. look post#7

  10. #10
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    Quote Originally Posted by lancer102rus View Post
    just small change:
    Hey there,

    Sorry to bug you again, but I was able to try and put the macro into my larger project and it isn't working correctly.

    In the larger project, I've got a complicated array formula - though when I'm applying these comments to the formula (which is transferring correctly, so that does work), it's changing the value of the cell itself. It replaces the destination cell with the value of the cell where it's pulling the comment from in place of the array formula I'm using.

    Is there any way to bypass this and maintain a formula while still moving over the comments?

  11. #11
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    Quote Originally Posted by levitt View Post
    Is there any way to bypass this and maintain a formula while still moving over the comments?
    And to follow up briefly, this is the array I'm using:

    Please Login or Register  to view this content.
    If I could add to the macro to, after running the comments, re-run that Array (which does require shift enter to apply, mind you) is a workaround that I see could work.

    --edit--

    Not to further complicate things, but the "1" in ...
    Please Login or Register  to view this content.
    towards the end is variable as well and changes as it would be dragged down.
    Last edited by levitt; 08-06-2014 at 12:45 AM.

  12. #12
    Forum Contributor lancer102rus's Avatar
    Join Date
    04-02-2014
    Location
    Ufa
    MS-Off Ver
    Excel 2010, 2013
    Posts
    252

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    here it is
    Please Login or Register  to view this content.
    Last edited by lancer102rus; 08-06-2014 at 01:20 AM.

  13. #13
    Registered User
    Join Date
    05-20-2013
    Location
    detroit, michigan
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    Quote Originally Posted by lancer102rus View Post
    here it is
    You are seriously an absolute godsend!! Let me know if you're ever in Michigan and I'm buying you a beer.

  14. #14
    Forum Contributor lancer102rus's Avatar
    Join Date
    04-02-2014
    Location
    Ufa
    MS-Off Ver
    Excel 2010, 2013
    Posts
    252

    Re: Adding If / Then Logic to a Copy / Paste VBA Macro

    catch you up on that

+ 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. 2 Macro's: only vertical copy/paste action and copy-paste 14 columns to the right.
    By vdongen in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2010, 10:34 AM
  2. Multiple Copy and Paste with Adding
    By carluk in forum Excel General
    Replies: 2
    Last Post: 11-28-2008, 12:37 PM
  3. VBA code for copy/paste logic needed
    By overlook1977 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-05-2008, 10:20 PM
  4. [SOLVED] Simple Copy-Paste logic not working
    By Brian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-29-2005, 02:40 PM
  5. copy macro with if logic?
    By copyright2000 in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 03-17-2005, 10:12 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1