VBA Random

[peteypie76]

Hi,

Thanks in advance for looking to help me.

I have 5 lists: Animals, Food, Places, Names and Drinks there are 20 things in each list.

I need to populate a 5 x 5 grid with random selections from these lists - but there can be no duplicates. Column 1 on the 5x5 grid needs to pick from the animals list, column 2 from the 5x5 grid from the foods list etc.

I need to repeat this process many times for more 5x5 grids starting afresh for each grid.

Is anyone able to help? I am not very good at VBA/VBScript

Thanks
Petey

The image is a mock up the 5x5 grids are on the left the lists to choose from is on the right. For example B10 needs to be randomly chosen from H6:H14. B11 needs to randomly choose a name from H6:H14 but ensure that the name in B10 is not chose as it would be a duplicate. Hope this makes sense.

 

[Akuini]

Hi,

Thanks in advance for looking to help me.

I have 5 lists: Animals, Food, Places, Names and Drinks there are 20 things in each list.

I need to populate a 5 x 5 grid with random selections from these lists - but there can be no duplicates. Column 1 on the 5x5 grid needs to pick from the animals list, column 2 from the 5x5 grid from the foods list etc.

I need to repeat this process many times for more 5x5 grids starting afresh for each grid.

Is anyone able to help? I am not very good at VBA/VBScript

Thanks

1. Can you post the data sample as a table not just animage?
2. The layout of your real data, is it the same as your example above, so the 5 lists 'Animals, Food, Places, Names and Drinks' is in col H:L with header at row 5?

 

[Rick Rothstein]

I think the following macro may do what you are looking for. The macro will automatically adapt if you choose to make your data table larger than 5 columns by 20 rows... all you have to do is tell it any single cell address (shown in red below) within the table. You can also change the number of rows output by the macro (the code will always use the same number of columns as the data table contains) by changing the number assigned to the GridRowCount constant (shown in green below). Finally, you can change the column the output starts outputting to by changing the blue highlighted column letter designation... output will always start on Row 3 (the math was easier doing it that way) and leave 2 blank rows between each grid thereafter.

[table="width: 500"]
[tr]
	[td]Sub RandomGrids()
  Dim R As Long, C As Long, Rws As Long, Cols As Long, Cnt As Long, RandomIndex As Long
  Dim Data As Variant, Arr As Variant, Tmp As Variant, Result As Variant
  Const GridRowCount As Long = [B][COLOR="#008000"][SIZE=4]5[/SIZE][/COLOR][/B]
  Randomize
  Data = Range("[B][COLOR="#FF0000"][SIZE=4]H5[/SIZE][/COLOR][/B]").CurrentRegion
  Rws = UBound(Data, 1)
  Cols = UBound(Data, 2)
  ReDim Result(1 To GridRowCount, 1 To Cols)
  For C = 1 To Cols
    Arr = Application.Index(Data, Evaluate("ROW(2:" & Rws & ")"), C)
    For Cnt = Rws - 1 To 1 Step -1
      RandomIndex = Int(Cnt * Rnd + 1)
      Tmp = Arr(RandomIndex, 1)
      Arr(RandomIndex, 1) = Arr(Cnt, 1)
      Arr(Cnt, 1) = Tmp
    Next
    For R = 1 To UBound(Result)
      Result(R, C) = Arr(R, 1)
    Next
  Next
  Cells(Rows.Count, "[B][COLOR="#0000FF"][SIZE=4]B[/SIZE][/COLOR][/B]").End(xlUp).Offset(3).Resize(GridRowCount, Cols) = Result
End Sub[/td]
[/tr]
[/table]