Hi all,
First time posting here, apologies if the formatting is incorrect. I have been using the same macro on 3 or 4 computers for a couple of years, all it is doing is selecting a random set of data based on the quanity specified on a different sheet. The script is called from a much larger script. Since the most recent update of office 365 (to the white poster on startup rather than the green one) it fails on a single line.
When I click debug it is the line, With CreateObject("System.Collections.SortedList"), where the error occurs. This particular line is used in a few other places in the larger script at it fails in all subs but I can't figure out why. It still works on a PC with Excel (Version 2110 Build 16.0.14527.20234) 32-bit but fails on (Version 2110 Build 16.0.14527.20270) 64-bit.
Sub getproducts()
Dim Rng As Range, i2 As Long, sku_qty As Integer
sku_qty = Worksheets("Working").Range("I5").Value
Set Rng = Sheets("Products").Range("a1").CurrentRegion
Sheets("Random Products").Range("a1").CurrentRegion.Offset(1).ClearContents
With CreateObject("System.Collections.SortedList")
Randomize
For I = 2 To Rng.Rows.Count
.Item(Rnd) = Rng.Rows(I).Value
Next
For I = 0 To Application.Min(sku_qty - 1, .Count - 1)
Sheets("Random Products").Cells(I + 2, "a") _
.Resize(, Rng.Columns.Count).Value = .GetByIndex(I)
Next
End With
End Sub
Bookmarks