Original post, Create button to run formula on every sheet in workbook.
This is the code from the module to randomly pull tickets from a list of tickets on each sheet in the workbook.
Sub RandomTickets() Dim x, i As Long, ii As Long, ws As Excel.Worksheet For Each ws In Sheets If ws.Name <> ActiveSheet.Name And ws.Name <> "Utilization Data" And ws.Name <> "Current Roster" Then With ws x = .Cells(1).CurrentRegion.Columns(2) ' On Error Resume Next With CreateObject("system.collections.arraylist") For i = 1 To 4 ReTry: ii = Application.RandBetween(2, UBound(x, 1)) If Not .contains(x(ii, 1)) Then .Add x(ii, 1) Else GoTo ReTry End If Next ws.[h2:l2] = .toarray End With End With
I have a need to change the code as I cannot use CreateObject("system.collections.arraylist")
I'm working on a company computer and .Net Framework 3.5 isn't installed, which I found is a requirement for the arraylist. I can't install .NET myself, and I'm not sure if the company would push it corporate wide since others want to use this spreadsheet as well. So I need to find an alternative.
I read about using VB arrays or collections as an alternative, but got lost trying to convert the code to use a VB array or collection over the arraylist.