+ Reply to Thread
Results 1 to 19 of 19

Random numbers but 3 numbers in sequence not allowed.

  1. #1
    Registered User
    Join Date
    07-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Random numbers but 3 numbers in sequence not allowed.

    My worksheet has 20 columns and 500 rows. I wanted to populate my worksheet with random numbers: 6 numbers in every row. Problem is I do not want any row with 3 or more numbers in sequence eg 3-4-5 , 12-13-14, 15-16-17-18 etc. I try to modify some code I copy from somewhere to suit my need but the more I do, the more I get confused. Please help, somebody.

    Please Login or Register  to view this content.
    Last edited by Cutter; 07-28-2012 at 03:18 PM. Reason: Added code tags

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Random numbers but 3 numbers in sequence not allowed.

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: Random numbers but 3 numbers in sequence not allowed.

    It would be better/faster to do this with an array but this meets what you asked for.

    Please Login or Register  to view this content.
    Regards,

    Tom
    Last edited by tom.hogan; 07-28-2012 at 01:22 PM.

  4. #4
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Random numbers but 3 numbers in sequence not allowed.

    @ NewGen

    Welcome to the forum.

    Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
    Thanks.

  5. #5
    Registered User
    Join Date
    07-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Random numbers but 3 numbers in sequence not allowed.

    Patel45 and tom.hagan, thank you for your reply. It worked for patel45. Tom.hagan I am afraid I got a run time error "438" object doesn't support this property or method. Btw I am using Excel 2003.
    Thank you Cutter for your gentle reminder.

  6. #6
    Registered User
    Join Date
    07-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Random numbers but 3 numbers in sequence not allowed.

    Patel45, the code runs well but the result seems to be missing random number 1. All I got was 2 to 20 and the cells populated are from B to T instead of B to U. Only 19 columns were populated. Sorry.

  7. #7
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Random numbers but 3 numbers in sequence not allowed.

    eliminate line
    If Cells(j + 1, Number).Value = my(Number) - 1 Then GoTo NewNumber
    and try again

  8. #8
    Registered User
    Join Date
    07-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Random numbers but 3 numbers in sequence not allowed.

    Patel45,I now have all the 20 random numbers within columns B to U, but I still get 3 numbers in sequence in some rows. eg 11-12-13 which I do not want.

  9. #9
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Random numbers but 3 numbers in sequence not allowed.

    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: Random numbers but 3 numbers in sequence not allowed.

    Quote Originally Posted by NewGen View Post
    Patel45 and tom.hagan, thank you for your reply. It worked for patel45. Tom.hagan I am afraid I got a run time error "438" object doesn't support this property or method. Btw I am using Excel 2003.
    Thank you Cutter for your gentle reminder.
    Sorry, I used an Excel 2010 function, that's why you get the runtime error

    Tom

  11. #11
    Registered User
    Join Date
    07-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Random numbers but 3 numbers in sequence not allowed.

    Patel45, When I ran the code I managed to get random numbers without 3 consecutive numbers in sequence. It didn't quite appear right as some numbers may be in pairs such as 5-6 or 12-13 etc. I allowed pairs but not 3 or more consecutive numbers. I tried modifying your codes using And,Cells including changing Number > 2 but without success. Thank you for your patience.

  12. #12
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: Random numbers but 3 numbers in sequence not allowed.

    1) Is this homework – I’ve seen similar posts all over the web?
    2) Can you describe exacly what the output should look like? From Patel45s solution I infer that:
    a. Numbers must be between 1-20, inclusive
    b. There are 500 rows that must be filled out
    c. There are 20 columns that must be filled out
    d. Each column should have either a “” or a number, the number in the column should be the column “number” i.e. if column A has a number it is 1, if column E has a number it is 5. If this is correct, that means no duplicate numbers is also a requirement.
    e. You may not have three consecutive columns with numbers in them (this would mean three consecutive numbers).
    3) Two coding observations (especially if this is homework):
    a. You should define your variables with dimension statements
    b. You should build the matrix in a 20x500 array and then copy the array to the spreadsheet with one command after it is filled, this will greatly increase the speed of the macro.

    Tom

  13. #13
    Registered User
    Join Date
    07-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Random numbers but 3 numbers in sequence not allowed.

    Tom, I had searched the web for answer and even tried to modify them to suit my need, but none came close and I found difficult to modify.

  14. #14
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: Random numbers but 3 numbers in sequence not allowed.

    Can you answer my above questions?

    Thanks,

    Tom

  15. #15
    Registered User
    Join Date
    07-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Random numbers but 3 numbers in sequence not allowed.

    Tom, almost all the questions may be answered affirmatively. This problem was asked by one of my private students and I could not solve it. If you were to run my codes and one of Patel45's codes you will get every row containing 6 numbers. Problem is I may get few rows containing 2,3,4,5 or 6 random consecutive numbers. Yes. you were right about columns B,C,D,E and so on. They represent 1,2,3,4 and so on.
    In this problem, a row containing 6 random numbers with 3 or more consecutive numbers are not allowed. For example a row with three consecutive not allowed is 4,7,13,14,15,20 or a row with four consecutive numbers not allowed is 2,5,6,7,8,17. Notice in the former row 13,14 and 15 are three consecutive numbers while in the latter row 5,6,7 and 8 are four consecutive numbers. Note 4,5 and 6 consecutive numbers in a row are also not allowed.
    I am allowed at most 2 consecutive numbers in a row. eg 5,9,14,17,18,20 where 17 and 18 are 2 consecutive numbers.
    I hope this is clear

  16. #16
    Registered User
    Join Date
    07-28-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Random numbers but 3 numbers in sequence not allowed.

    I think I have found an answer, though not sleek but workable. That is to delete the entire row containing 3 or more consecutive numbers after populating the cells.

  17. #17
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Random numbers but 3 numbers in sequence not allowed.

    @ NewGen

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a new member of the forum, 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.

  18. #18
    Registered User
    Join Date
    07-16-2012
    Location
    Newcastle upon Tyne
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Random numbers but 3 numbers in sequence not allowed.

    Try this:


    Please Login or Register  to view this content.
    Last edited by stunn; 07-30-2012 at 03:40 PM.
    Steve D. a.k.a. Stephen Dunn

  19. #19
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: Random numbers but 3 numbers in sequence not allowed.

    This meets all your homework requirements
    Please Login or Register  to view this content.
    Regards,

    Tom
    Last edited by tom.hogan; 07-31-2012 at 08:18 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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