+ Reply to Thread
Results 1 to 9 of 9

VBA For / Do While Loop

  1. #1
    Registered User
    Join Date
    06-08-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    37

    Question VBA For / Do While Loop

    Hey all,

    I've created a macro that will generate 3 random numbers from column A into column C.

    However, I would like add a condition(s) to make sure that these 3 random numbers are not in Column B and have no been selected previously. If either of these conditioons are met, then a new random number is to be chosen.

    I'm sure there is a 'Do While' loop of some sort, but can't get it working. My (current) working code is below and as attached.

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: VBA For / Do While Loop

    This code should work:
    Please Login or Register  to view this content.
    Note, however, that your RandomNum will never equal 1 or the last cell (i.e. it ranges from 2 to 23 in your example, never 1 or 24). Was that intentional? If not, just change your equation.
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    06-08-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    37

    Re: VBA For / Do While Loop

    Perfect exactly what I was trying to do. I wasn't intending to have the range between 2 and 23. Where abouts in the equation I'm I modifying?

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: VBA For / Do While Loop

    To get a random integer between 1 and x use:
    Int(x*Rnd)+1

  5. #5
    Registered User
    Join Date
    06-08-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    37

    Re: VBA For / Do While Loop

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    That easy?

  6. #6
    Forum Contributor
    Join Date
    03-11-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    379

    Re: VBA For / Do While Loop

    @DHHM & @Pauley

    I test the code provide by Pauley & found an error.

    To test the code I copy first ten numbers from column A to column B.
    In column C I used
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I have attached image file, plz check the yellow color cell is common in all three columns.

    Let me know if I am missing anything.

    Regards,
    AM
    Attached Images Attached Images

  7. #7
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: VBA For / Do While Loop

    thats cause he only checks the first 3 rows for a duplicate

    change the Red number to fit your needs and the could should work perfectly
    Please Login or Register  to view this content.
    greets
    Loki

  8. #8
    Forum Contributor
    Join Date
    03-11-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    379

    Re: VBA For / Do While Loop

    Quote Originally Posted by LordLoki View Post
    thats cause he only checks the first 3 rows for a duplicate

    change the Red number to fit your needs and the could should work perfectly
    Please Login or Register  to view this content.
    greets
    Loki
    Thanks Loki

  9. #9
    Registered User
    Join Date
    06-08-2015
    Location
    Australia
    MS-Off Ver
    2013
    Posts
    37

    Re: VBA For / Do While Loop

    Thanks all.

+ 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. Find function in nested loop breaking down - not on first time through loop
    By adamstarr12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2014, 04:59 PM
  2. [SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)
    By nixon72 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-12-2013, 12:46 PM
  3. Why did an inner loop variable start overwriting the outer loop range suddenly?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-16-2012, 03:24 PM
  4. Replies: 0
    Last Post: 07-20-2010, 11:42 AM
  5. Loop a column on Sheet1 and loop a row on Sheet2 to find a match
    By johnnywinter in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-13-2009, 02:09 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