Please help to code has debug.

[motilulla]

Using Excel 2010
Hello,

When I run code it stop at line below with error 438 does not RandomizeColumn

.SortFields.Add2 Key:=rc.Offset(0, 1), SortOn:=xlSortOnValues, Order:=xlAscending

MrExcel Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			1	1.193	1.431	1.097	0	1.694	2.147	0	0	2.386	1.694	1.097	1.145	1.431	1.049
2			X	1.193	0	167	0	692	239	0	978	0	692	883	1.097	0	1.002
3			2	0	955	1.122	2.386	0	0	2.386	1.408	0	0	406	144	955	335
4				2.386	2.386	2.386	2.386	2.386	2.386	2.386	2.386	2.386	2.386	2.386	2.386	2.386	2.386
5			Combi	P1	P2	P3	P4	P5	P6	P7	P8	P9	P10	P11	P12	P13	P14
6			1	1	0,887
7			2	1	0,987
8			3	1	0,488
9			4	1	0,275
10			5	1	0,544
11			6	1	0,717
12			7	1	0,661
13			8	1	0,546
14			9	1	0,017
15			10	1	0,925
16			11	1	0,959
17			12	1	0,025
18			13	1	0,882
19			14	1	0,477
20			15	1	0,687
21			16	1	0,372
22			17	1	0,573
23			18	1	0,935
24			19	1	0,865
25			20	1	0,906
26			21	1	0,995
27			22	1	0,481
28			23	1	0,887
29			24	1	0,793
30			25	1	0,447
31			26	1	0,253
32			27	1	0,623
33			28	1	0,73
34			29	1	0,228
35			30	1	0,279
36			31	1	0,693
37			32	1	0,884
38			33	1	0,712
39			34	1	0,886
40			35	1	0,503
41			36	1	0,203
42			37	1	0,279
43			38	1	0,504
44			39	1	0,716
45			40	1	0,05
46			41	1	0,59
47			42	1	0,895
48			43	1	0,538
49			44	1	0,064
50			45	1	0,632
New Request Fill By Value

Cell Formulas
Range	Formula
D4:Q4	D4	=SUM(D1:D3)
E6:E50	E6	=RAND()

Sub FillByValue()
    Dim c As Integer
    Dim d As Integer
    Dim e As Integer
    Dim r As Range
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("New Request Fill By Value")
    
    With sh.Range("D6:Q3505")
        .ClearContents
        For Each r In .Rows(1).Cells
        c = 0
        d = 0
        e = 0

        If Cells(1, r.Column).Value > 0 Then
            c = Cells(1, r.Column).Value
            r.Resize(c).Value = Range("C1").Value
        End If
        If Cells(2, r.Column).Value > 0 Then
            d = Cells(2, r.Column).Value
            r.Offset(c).Resize(d).Value = Range("C2").Value
        End If
        If Cells(3, r.Column).Value > 0 Then
            e = Cells(3, r.Column).Value
            r.Offset(c + d).Resize(e).Value = Range("C3").Value
        End If
        RandomizeColumn r.Resize(c + d + e)
        Next r

    End With
End Sub

Sub RandomizeColumn(rc As Range)
    rc.Offset(0, 1).FormulaR1C1 = "=RAND()"
    With rc.Parent.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=rc.Offset(0, 1), SortOn:=xlSortOnValues, Order:=xlAscending
        .SetRange rc.Resize(, 2)
        .Header = xlNo
        .Orientation = xlTopToBottom
        .Apply
    End With
    rc.Offset(0, 1).ClearContents
End Sub

Regards,
Moti

 

[RoryA]

Use .Add not .Add2 which did not exist in 2010.

 

[motilulla]

Use .Add not .Add2 which did not exist in 2010.

RoryA, I spent hours to figure it out lot of search in google but no luck and you solve it in minutes I appreciate your help. Good Luck!

Best Regards,
Moti

 

[motilulla]

I have one more question I have one formula which work under Excel 365, I want to use it with my excel 2010 post #1 in D6 and copied across to Q50. Is it convertible? If not please suggest one.

=LET(a,REPT($C$1&" ",D1),b,REPT($C$2&" ",D2),c,REPT($C$3&" ",D3),SORTBY(TEXTSPLIT(TRIM(a&b&c),," "),RANDARRAY(D1+D2+D3)))

Regards,
Moti

 

[RoryA]

That is a completely different issue and should be posted as a different question please. (none of those functions have simple equivalents)

 

[motilulla]

That is a completely different issue and should be posted as a different question please. (none of those functions have simple equivalents)

RoryA, thank you for replay, perfect I will open new thread.

Have a nice day. Good Luck!

Best Regards,
Moti