Use excel to list the combination of 10 selected 4, application submitted to the subscript crossing-CodePudding

Use excel to list the combination of 10 selected 4, application submitted to the subscript crossing the line, a great god, please help!

 Sub combination () 
Dim arr_S (1 To 10) 
Dim arr_O 
Dim I, J, K1, K2, K3, K4 As Integer 
I=Sheet1. Range (" A65536 "). The End (xlUp). Row 
For J=1 To I - 1 'To the original sequence array assignment 
Arr_S (J)=Cells (J + 1, 1) 
Next J 

I=I - 1 'the number of original data 
J=I * (I - 1) * (I - 2) * (I - 3)/(2 * 1 * 3 * 4) 'combination by 4 column, calculate the number of the target array 
ReDim arr_O (1 To J, 1 To 4) 
J=1 
For K1=1 To 3 
 I -For K2=K1 + 1 To 2 
 I -For K3=K2 + 1 To 1 
 I -For K4=K3 + 1 To the I 
Arr_O (J, 1)=arr_S (K1) 
Arr_O (J, 2)=arr_S (K2) 
Arr_O (J, 3)=arr_S (K3) 
Arr_O (J, 4)=arr_S (K4) 
J=J + 1 
Next K4 
Next K3 
Next K2 
Next K1 
For I=1 To J - 1 
Cells (I + 1, 3)=arr_O (I, 1) 
Cells (I + 1, 4)=arr_O (I, 2) 
Cells (I + 1, 5)=arr_O (I, 3) 
Cells (I + 1, 6)=arr_O (I, 4) 
Next I 
End Sub

CodePudding user response:

Isn't in give arr_S subscript cross-border assignment?

Have since been identified for the data is 10, so why keep with you the number of "data" :
I=Sheet1. Range (" A65536 "). The End (xlUp). Row
That aren't readily available way to calculate?

CodePudding user response:

Why run, I changed, but not any data out,

 Sub combination () 
Dim arr_S (1 To 10) 
Dim arr_O 
Dim I, J, K1, K2, K3, K4 As Integer 
I=11 'Sheet1. Range (" A65536 "). The End (xlUp). Row 
For J=1 To I - 1 'To the original sequence array assignment 
Arr_S (J)=Cells (J + 1, 1) 
Next J 

I=I - 1 'the number of original data 
J=I * (I - 1) * (I - 2) * (I - 3)/(2 * 1 * 3 * 4) 'combination by 4 column, calculate the number of the target array 
ReDim arr_O (1 To J, 1 To 4) 
J=1 
For K1=1 To 3 
 I -For K2=K1 + 1 To 2 
 I -For K3=K2 + 1 To 1 
 I -For K4=K3 + 1 To the I 
Arr_O (J, 1)=arr_S (K1) 
Arr_O (J, 2)=arr_S (K2) 
Arr_O (J, 3)=arr_S (K3) 
Arr_O (J, 4)=arr_S (K4) 
J=J + 1 
Next K4 
Next K3 
Next K2 
Next K1 
For I=1 To J - 1 
Cells (I + 1, 3)=arr_O (I, 1) 
Cells (I + 1, 4)=arr_O (I, 2) 
Cells (I + 1, 5)=arr_O (I, 3) 
Cells (I + 1, 6)=arr_O (I, 4) 
Next I 
End Sub

CodePudding user response:

In 2 f this code, you can still run normally, and get the result, (but) under specific "operating conditions" limit
Main problem is that does not pay attention to "data types", not rigorous logic,

For example, you have a "variable definitions" writing problem, cause a lot of the Variant types of variables (which is "efficiency" problem),
Dim I, J, K1, K2, K3, K4 As Integer
This result is only K4 type Integer, and several of the other are the Variant types,

Also, when you visit Cells object, which is not explicitly specify "table", it is according to the worksheet "activity" for a visit,
If you run this code, the workbook in the selected table is not your Sheet1, so are likely to be empty data, or data is not correct,
This can be in "to the original sequence array assignment" For loop after verification,
You said "no data", should be your runtime activity worksheet is not Sheet1, read first assignment is blank data,
(I=Sheet1. According to your original code Range (" A65536 "). The End (xlUp). The Row, speculated that your data sequence is in Sheet1)