Tests Copying, Pasting, API Cliipboard issues. and Rough notes on Advanced API stuff

**DocAElstein** · 12-30-2018, 08:47 PM

Code for Yassser here:
http://www.eileenslounge.com/viewtop...=31529#p243999

Code:

Option Explicit
'I have numbers from 1 to 2319 made in groups in different numbers (in ten groups) as shown in column F
'How can I get random distribution for those group to have the same range of numbers from 1 to 2319
'but in different order and at the same time to have the same number inside each group
'Example
'Group 6 from 1267 - 1489 >> the number inside that group is 223
'Suppose the random choice make this group the first one so the expected result would be 1 - 223
'
'then suppose the second selected group is group 8 which is 1699 - 1938 >> the number inside that group is 240
'So that new group in the expected result would start at 224
'(which is the last number in the previous result and the finish number would be 463
'
'...
'Is it possible to do that in random order?
'
Sub RandomDistribution4Numbers() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31529
Dim Ws1 As Worksheet: Set Ws1 = Worksheets("Sheet1")
Dim arrSN() As Variant: Let arrSN() = Ws1.Range("F2:F" & Ws1.Range("F" & Rows.Count & "").End(xlUp).Row & "").Value ' range of current SNs
Dim LstGrpStp As Long: Let LstGrpStp = 0 ' last number used at end of random number group
Dim arrGrpsOut() As String: ReDim arrGrpsOut(1 To UBound(arrSN(), 1), 1 To 1) ' Array for output values
    Do ' we loop while we have not yet filled all of the output array, arrGrpsOut()
    Dim Rnd1ToUBnd As Long ' For a random array indicie from 1 to the UBound "row" of the input, (and output), arraysd
    Randomize: Let Rnd1ToUBnd = Int(UBound(arrSN(), 1) * Rnd) + 1
        If arrGrpsOut(Rnd1ToUBnd, 1) = "" Then ' Not yet filled this element in output array, so do the main stuff
        Dim OutElsFilled As Long: Let OutElsFilled = OutElsFilled + 1 ' count of number of outup array elements filled
        ' split F column (arrSN())  numbers to get range of numbers
        Dim SpltRng() As String: Let SpltRng() = Split(arrSN(Rnd1ToUBnd, 1), " - ", 2, vbBinaryCompare)
        Dim Rng As Long: Let Rng = SpltRng(1) - SpltRng(0) ' Range of numbers
        Dim Stt As Long, Stp As Long: Let Stt = LstGrpStp + 1: Let Stp = LstGrpStp + Rng + 1 ' Start and stop of range of numbers
        ' build output array with the numbers
         Let arrGrpsOut(Rnd1ToUBnd, 1) = Stt & " - " & Stp
         Let LstGrpStp = Stp ' Last highest used number
        Else ' If we come here then our random number must of been for an indicie of an array element already filled - so this probably makes the code a bit inefficient
        End If
    Loop While OutElsFilled < UBound(arrSN(), 1) ' we loop while we have not yet filled all of the output array, arrGrpsOut(), which is determined by if we did the main stuff as many times as there are elements in the input/Output arrays

 Let Ws1.Range("G2").Resize(UBound(arrSN(), 1)).Value = arrGrpsOut
End Sub
'





Sub RandomizeGroups() ' Hans code ' http://www.eileenslounge.com/viewtopic.php?f=30&t=31529#p244006
    Dim arr   As Variant
    Dim lb    As Long
    Dim ub    As Long
    Dim i     As Long
    Dim j     As Long
    Dim tmp   As Long
    Dim n     As Long
    Dim idx() As Long
    Dim itm() As String
    Dim grp() As String
    arr = Range("F2:F11").Value
    lb = LBound(arr, 1)
    ub = UBound(arr, 1)
    ReDim idx(lb To ub)
    ReDim grp(lb To ub)
    For i = lb To ub
        idx(i) = i
    Next i
    For i = lb To ub
        j = Application.RandBetween(lb, ub)
        tmp = idx(i)
        idx(i) = idx(j)
        idx(j) = tmp
    Next i
    n = 1
    For i = lb To ub
        itm = Split(arr(idx(i), 1), " - ")
        grp(idx(i)) = n & " - " & n + itm(1) - itm(0)
        n = n + itm(1) - itm(0) + 1
    Next i
    Range("G2:G11").Value = Application.Transpose(grp)
End Sub

Typical results from my code are shown in column G. ( The code works on the data from column F )

_____ Workbook: Sample.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	E	F	G	H	I
1	for illustration	SN		Some expected result	Number inside Group
2	1	1 - 244	923 - 1166		244
3	2	245 - 448	1 - 204		204
4	3	449 - 750	398 - 699		302
5	4	751 - 1003	1879 - 2131		253
6	5	1004 - 1266	1167 - 1429		263
7	6	1267 - 1489	700 - 922	1 - 223	223
8	7	1490 - 1698	1430 - 1638		209
9	8	1699 - 1938	1639 - 1878	224 - 463	240
10	9	1939 - 2126	2132 - 2319		188
11	10	2127 - 2319	205 - 397		193

Worksheet: Sheet1

here below a few more runs, showing just column G
_____ Workbook: Sample.xlsm ( Using Excel 2007 32 bit )

_Row\^Col	G
1
2	591 - 834
3	835 - 1038
4	1502 - 1803
5	2067 - 2319
6	1804 - 2066
7	1279 - 1501
8	382 - 590
9	1039 - 1278
10	194 - 381
11	1 - 193

Worksheet: Sheet1

_____ Workbook: Sample.xlsm ( Using Excel 2007 32 bit )

254 - 497	2076 - 2319	1470 - 1713	638 - 881
498 - 701	517 - 720	1923 - 2126	1 - 204
1174 - 1475	1774 - 2075	705 - 1006	2018 - 2319
1 - 253	264 - 516	264 - 516	1354 - 1606
911 - 1173	1 - 263	1 - 263	882 - 1144
1476 - 1698	1551 - 1773	1247 - 1469	1607 - 1829
702 - 910	1342 - 1550	1714 - 1922	1145 - 1353
1892 - 2131	721 - 960	1007 - 1246	205 - 444
2132 - 2319	1154 - 1341	517 - 704	1830 - 2017
1699 - 1891	961 - 1153	2127 - 2319	445 - 637