+ Reply to Thread
Results 1 to 10 of 10

For Next Loop with an if statment (or replacing an if statment ?)

  1. #1
    Registered User
    Join Date
    12-21-2020
    Location
    Virginia, US
    MS-Off Ver
    365
    Posts
    8

    Question For Next Loop with an if statment (or replacing an if statment ?)

    Hello.
    Open to suggestions on how to best accomplish the following. Thanks in advance to anyone who can help:

    I'm working on an excel macro that compares the value in a cell against the value in another series of cells in another column.
    If there is a match, then there is a copy paste done of cells in that same row and it's pasted to a worksheetname that matches the value of another cell in that row.
    I have the part about worksheet name (thanks to a helpful member in this forum). What I am now looking for is a way to run through the comparisons with a for next instead of a very long set of if/then statements
    Below are the first 3 if then statements (there's over 100 rows/comparisons that need to be done).

    Please Login or Register  to view this content.
    ......
    Also, after running through that if/else statement above 100 times (comparing a2 to ad2 thru ad 101), it then needs to run similar if/else satements
    for row 3 100 times (coparing a3 to ad2 to ad101), like below.
    So not sure if there's a way to accomplish both of these comparisons in one for next loop or if it needs to be 2 separate for next loops.

    Please Login or Register  to view this content.
    ----

    Thanks to anyone who has read through all this and extra thanks to anyone who can help.
    Last edited by MikeGee; 12-23-2020 at 01:24 PM.

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

    Re: For Next Loop with an if statment (or replacing an if statment ?)

    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 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

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,209

    Re: For Next Loop with an if statment (or replacing an if statment ?)

    Try something like this:

    Please Login or Register  to view this content.
    Rory
    I drink, and I know things

  4. #4
    Registered User
    Join Date
    12-21-2020
    Location
    Virginia, US
    MS-Off Ver
    365
    Posts
    8

    Re: For Next Loop with an if statment (or replacing an if statment ?)

    Rory -
    Thanks very much. This does exactly what I asked.
    Two things I realized I didn't ask:
    1. The value in column B can appear more than once and thus have more than one set of values in in d & e to copy and then paste.
    The solution you provided does the paste each time into a1 so the subsequent pastes write over eachother and only the last one appears. (again, i realize now that the reason for this undesired outcome is due to my request not thinking through everything). So I need each subsequent match to paste one row below the last one on the destination sheet. As example if there are 3 matches, then first would go into a1, 2nd into a2, and 3rd into a3.
    2. This is related to #1, but the first copy/paste resulting from the first match needs to paste into the first open row of data in column A. So as an example, if the destination sheet as data in a1, a2, and a3 already, then the the first paste needs to go into a4 (and then subsequent matches starting in a5).
    Any chance there's an easy way to incorporate the pieces into your great solution provided?

    Thanks very much for your help.

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,209

    Re: For Next Loop with an if statment (or replacing an if statment ?)

    Can you post a workbook (data can be fake but should be representative, and the structure should match your actual workbook). It's going to be simplest to use a filter I suspect, assuming that the sheet name values in AC are the same for any given value in column B?

  6. #6
    Registered User
    Join Date
    12-21-2020
    Location
    Virginia, US
    MS-Off Ver
    365
    Posts
    8

    Re: For Next Loop with an if statment (or replacing an if statment ?)

    Hi RoryA-
    As requested/suggested, attached is a sample file with dummy data.
    I put a line of data already in place for a couple tabs so the part about finding first row with data and going one below that to start can be tested.
    As you can see the values in AC are not always the same as in B. For example, Column B has values of Blue1, Blue2, and Blue3 that go to the sheetname of Blue which is in AC. In some cases the values do match (like values of Belgium in B and AC) but not all.
    Hope you and family are enjoying the holidays and thanks so much for your help. (same goes to anyone else who wants to chime in on this thread!)

    -Mike Gee
    Attached Files Attached Files
    Last edited by MikeGee; 12-29-2020 at 01:38 AM.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    6,209

    Re: For Next Loop with an if statment (or replacing an if statment ?)

    Just a small change then:

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    12-21-2020
    Location
    Virginia, US
    MS-Off Ver
    365
    Posts
    8

    Re: For Next Loop with an if statment (or replacing an if statment ?)

    RoryA-
    This is perfect. Accomplishes exactly what is needed.
    Thank you VERY much for your posts/responses.

    -Mike G

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,471

    Re: For Next Loop with an if statment (or replacing an if statment ?)

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Registered User
    Join Date
    12-21-2020
    Location
    Virginia, US
    MS-Off Ver
    365
    Posts
    8

    Re: For Next Loop with an if statment (or replacing an if statment ?)

    Ali-
    Thanks for advice on how to add reputation and reminder to mark as solved.
    Great forum!

+ 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] If Statment loop
    By Rachel5694 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-20-2018, 08:20 AM
  2. [SOLVED] for and next loop with if statment
    By mike02 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-17-2013, 05:04 PM
  3. Replies: 2
    Last Post: 06-06-2012, 10:59 PM
  4. IF statment
    By Tortus in forum Excel General
    Replies: 2
    Last Post: 05-19-2011, 05:15 PM
  5. IF Statment Loop in VB
    By Philmillar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-31-2011, 10:13 AM
  6. [SOLVED] IF statment within IF statment?
    By DaZombie in forum Excel General
    Replies: 2
    Last Post: 10-27-2010, 12:33 PM
  7. End a DO WHILE LOOP WITH INTERNAL IF STATMENT?
    By 77anders in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2009, 04:54 AM

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