VBA help needed

Copper Contributor

Hi

I am trying to create an excel spreadsheet for a football tipping competition (for family & friends)

I am using Excel 365.

I asked this question to an AI bot program 

 

"I have an excel sheet with 27 tables named table 1 to table 27, I need to fill a table with 27 columns and 48 rows, I can only use the names from table 1 to randomly but equally fill all 48 rows in column 1 in the table, the same with table 2 to fill column 2 and so on through to table and column 27.

The names on each row cannot be repeated more than twice."

 

It has generated this script.

 

{

  "vba_script": "Sub FillTables()\n    Dim ws As Worksheet\n    Set ws = ThisWorkbook.Sheets(\"Sheet1\")\n    Dim i As Integer\n    Dim j As Integer\n    Dim tableNumber As Integer\n    Dim nameIndex As Integer\n    Dim usedNames As New Collection\n    For i = 1 To 48\n        For j = 1 To 27\n            Do\r\n                tableNumber = Int((27 - 1 + 1) * Rnd + 1)\r\n            Loop While usedNamesCount(ws.ListObjects(\"Table\" & tableNumber).ListColumns(j).DataBodyRange.Value) >= 2\r\n            usedNames.Add ws.ListObjects(\"Table\" & tableNumber).ListColumns(j).DataBodyRange.Value\r\n            ws.Cells(i, j).Value = ws.ListObjects(\"Table\" & tableNumber).ListColumns(j).DataBodyRange.Value\r\n        Next j\n    Next i\nEnd Sub\n\nFunction usedNamesCount(name As String) As Integer\n    On Error Resume Next\n    usedNamesCount = usedNames(name)\nEnd Function"

}

 

I am only a novice with Excel & was wondering if anyone would like to have a go at it for me.

Thanks

Bill

 

0 Replies