In one of the earlier topics we learned how to generate Random numbers in Excel & Google Spreadsheet. Now, we will see how to get values from Random Columns using the same function. This is kind of applying the Rand function little bit different application that we usually do.
These random number combinations are widely used in Games sequence generations, AI & in password combination generators. These are only few uses of random number generation. I wrote this topic based on the query raised by ‘Ravi Mangal’ on one of my recent article related to solving a Sudoku puzzle.
I am not sure whether he required this also for any Game solving or Designing VBA code.Here is the code that will Pick a Column & Row Randomly from sheet1, Read its value and place it another sheet.1234567891011121314151617181920Option ExplicitPublic Sub Random_Number_Column_Pick()'Variables definition - Code from Officetricks.comDim rndRow As Integer, rndCol As IntegerDim oRow As IntegerFor oRow = 1 To 5 'Pick 5 Numbers randomly from Worksheet1VBA.RandomizerndRow = VBA.Round(VBA.Rnd(1) * 10, 0) 'Generate Random Row Number between 1 and 10VBA.RandomizerndCol = VBA.Round(VBA.Rnd(1) * 26, 0) 'Random Column between 1 and 26 i.e., A - ZThisWorkbook.Sheets(2).Cells(oRow, 1) = rndRowThisWorkbook.Sheets(2).Cells(oRow, 2) = rndColThisWorkbook.Sheets(2).Cells(oRow, 3) = ThisWorkbook.Sheets(2).Cells(rndRow, rndCol)Next oRowEnd Sub
It is all pretty much a simple to execute the code if you learn basics of Excel VBA coding. Ensure that you have enough values in sheet1 before testing it. Otherwise it will end up in writing empty cell values to worksheet2.
This is how we can pick any row or column from Excel worksheets randomly and automate the results. With this strong function you can even build a poker or any gambling games in Excel.
Pick Multiple Columns using Random Numbers in Excel