Random numbers but 3 numbers in sequence not allowed.

Please Login or Register  to view this content.

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

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.

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.

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

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.

Please Login or Register  to view this content.

Originally Posted by NewGen

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

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.

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

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.

Can you answer my above questions?

Thanks,

Tom

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

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.

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