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.
Option Explicit Public Sub Random_Number_Column_Pick() 'Variables definition - Code from Officetricks.com Dim rndRow As Integer, rndCol As Integer Dim oRow As Integer For oRow = 1 To 5 'Pick 5 Numbers randomly from Worksheet1 VBA.Randomize rndRow = VBA.Round(VBA.Rnd(1) * 10, 0) 'Generate Random Row Number between 1 and 10 VBA.Randomize rndCol = VBA.Round(VBA.Rnd(1) * 26, 0) 'Random Column between 1 and 26 i.e., A - Z ThisWorkbook.Sheets(2).Cells(oRow, 1) = rndRow ThisWorkbook.Sheets(2).Cells(oRow, 2) = rndCol ThisWorkbook.Sheets(2).Cells(oRow, 3) = ThisWorkbook.Sheets(2).Cells(rndRow, rndCol) Next oRow End 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.