vba convert the formula combin into code

[montecarlo2012]

Hi.

Combin(i$5,6)-IF(I$5-5B6>0,COMBIN(I$5-B6,6),0)-IF(I$5-4-C6>0,COMBIN(I$5-C6,5),0)-IF(I$5-3-d6>0,COMBIN(I$5-D6,4)

I would like to use a code for this formula, rather than scroll down or up all the time.
Any help for this Please.

 

[StephenCrump]

It's not clear what you are doing, and why you need to scroll up or down?

Are you copying this formula down a column? What values do you have in B6:D6? What about B7:D7, B8:D8 etc?

Can you show us your layout via a screenshot, preferably using the Forum's XL2BB add-in.

 

[montecarlo2012]

StephenCrump thank you Sir for your reply.
this is what I have:
<tbody>

A	B	C	D	E	F	G	H	I	J	K
5/8/2019​	1​	11​	12​	24​	31​	33​				1,643,117.00
5/4/2019​	1​	4​	14​	33​	36​	43​				608,539.00
5/1/2019​	18​	27​	28​	30​	35​	43​				21,253,956.00
4/27/2019​	1​	17​	27​	36​	42​	51​		53​		2,206,281.00
4/24/2019​	2​	4​	21​	27​	32​	45​				3,002,358.00
4/20/2019​	4​	15​	19​	23​	31​	53​				8,421,055.00
4/17/2019​	2​	25​	31​	45​	52​	53​				4,842,369.00
4/13/2019​	5​	18​	28​	37​	42​	49​				10,348,067.00
4/10/2019​	18​	22​	34​	39​	48​	53​				21,160,159.00
4/6/2019​	4​	8​	36​	37​	40​	50​				7,748,884.00
4/3/2019​	9​	18​	22​	37​	44​	46​				15,541,728.00
3/30/2019​	10​	13​	23​	49​	51​	53​				16,175,608.00
3/27/2019​	13​	17​	19​	23​	39​	50​				18,691,578.00

</tbody>
When I update this array always is in top, reason why I say scroll up or if I copy the array somewhere else scroll down, and this array is B2:G LastRow, is a "dynamic array". at this moment this array is ("B2:G2752").
Thank you Mr. StephenCrump.

 

[StephenCrump]

Sorry, I still don't understand the problem. Can you please describe in more detail what you are trying to do?

I am guessing (based on our discussions in other threads) that you are writing these formulae using VBA, and that you want your code to put similar formulae into other cells? If so, it would help if you let us know what these other formulae are, and where you want to put them.

And if you are already using code, can you please post it, thanks.

 

[montecarlo2012]

Really sorry I am not clear.
what I am trying to do is to get the index number of any combination of the 6 numbers taking out of 53, so is about 23 millions combinations, I hava a list of 2.500 more or less and growing I would like every time I update the list, just play the code I get the index number of the next combination. And on I5 I have the number 53, I would like to move to B1.
In the example column K have the index numbers, but this is not about the formula is working, is that a code is better for me. sorry.
And this formula is not connected with the other threads before, so I have one formula, and no codes at all; where I want to putting, what about column H, would be fine.
thank you Mr. Stephen Crump.

 

[StephenCrump]

By index number, I think you mean something like this?

GetNthCombination.xlsm
	A	B	C	D	E	F	G
1	Index	Combination	---->
2	1	1	2	3	4	5	6
3	2	1	2	3	4	5	7
4	3	1	2	3	4	5	8
5	4	1	2	3	4	5	9
6	100	1	2	3	4	7	12
7	1,000	1	2	3	4	34	48
8	100,000	1	2	8	35	40	42
9	1,000,000	1	6	17	19	37	41
10	22,957,480	48	49	50	51	52	53
Sheet1

Cell Formulas
Range	Formula
B2:G10	B2	=GetNthCombination(53,6,A2)
A10	A10	=COMBIN(53,6)
Dynamic array formulas.

I have the following code to go from, for example, Index 1,000,000 ----> Combination 1, 6, 17, 19, 37, 41.

Function GetNthCombination(N As Long, r As Long, ByVal cCombinationNo As Currency) As Long()

    Dim lCombination() As Long, i As Long, j As Long
    Dim cTemp As Currency
    ReDim lCombination(1 To r)
    
    j = 0
    For i = 1 To r
        Do
            j = j + 1
            cTemp = Round(Application.Combin(N - j, r - i), 0)
            If cCombinationNo <= cTemp Then
                lCombination(i) = j
                Exit Do
            End If
            cCombinationNo = cCombinationNo - cTemp
        Loop
    Next i
    
    GetNthCombination = lCombination

End Function

I think you want to go in the opposite direction, e.g. you have Combination 1, 6, 17, 19, 37, 41, and you want the formula to return the index number 1,000,000?

 

[montecarlo2012]

Thank you Mr. Stephen Crump.
You are right, I have the combinations, and would like to see the index numbers for any combination I will upload to the sheet.
and also Thank you for the function, is really handy.
Thank you for your time Mr. Stephen Crump.

 

[StephenCrump]

Here's one way could code it:

GetNthCombination.xlsm
	A	B	C	D	E	F	G	H	I
1		Combination	---->						Index
2		1	2	3	4	5	6		1
3		1	2	3	4	5	7		2
4		1	2	3	4	5	8		3
5		1	2	3	4	5	9		4
6		1	2	3	4	7	12		100
7		1	2	3	4	34	48		1,000
8		1	2	8	35	40	42		100,000
9		1	6	17	19	37	41		1,000,000
10		48	49	50	51	52	53		22,957,480
Sheet2

Cell Formulas
Range	Formula
I2:I10	I2	=GetIndexNumber(53,6,B2:G2)

Function GetIndexNumber(N As Long, r As Long, Vector As Variant) As LongLong

    Dim Combination As Variant
    Dim i As Long
    Dim Total As LongLong
    
    Combination = Vector
    If TypeOf Vector Is Range Then
        Combination = Application.Transpose(Vector)
        If Vector.Columns.Count > 1 Then Combination = Application.Transpose(Combination)
    End If
    Total = Application.Combin(N, r)
    
    On Error Resume Next
    For i = LBound(Combination) To UBound(Combination)
        Total = Total - Application.Combin(N - Combination(i), r + LBound(Combination) - i)
    Next i
    On Error GoTo 0
    
    GetIndexNumber = Total

End Function

 

[montecarlo2012]

Hi, Sir.
Thank you for your reply.
I use the formula and give me #NAME?, and the function, I insert a module in my IDE and not results,
Please excuse my coding level, if it is possible a regular sub where I can see the destination location, like range("O:O").

 

[StephenCrump]

See workbook attached: Box

GetNthCombination.xlsm
	A	B	C	D	E	F	G	H	I
1	MyN	53
2	MyR	6
3
4	Index	Combination	---->						Index
5	1	1	2	3	4	5	6		1
6	2	1	2	3	4	5	7		2
7	3	1	2	3	4	5	8		3
8	4	1	2	3	4	5	9		4
9	100	1	2	3	4	7	12		100
10	1,000	1	2	3	4	34	48		1,000
11	100,000	1	2	8	35	40	42		100,000
12	1,000,000	1	6	17	19	37	41		1,000,000
13	22,957,480	48	49	50	51	52	53		22,957,480
1

Cell Formulas
Range	Formula
B5:G13	B5	=GetNthCombination(MyN,MyR,A5)
A13	A13	=COMBIN(MyN,MyR)
I5:I13	I5	=GetIndexNumber(MyN,MyR,B5:G5)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Named Ranges
Name	Refers To	Cells
MyN	='1'!$B$1	A13:G13, B5:G12, I5:I13
MyR	='1'!$B$2	A13:G13, B5:G12, I5:I13

The formulae don't display well above:
Select B5:G5 and array-enter the formula: {=GetNthCombination(MyN,MyR,A5)}
Copy down the column.

Or for those with Excel 365 dynamic functions, simply enter B5: =GetNthCombination(MyN,MyR,A5)

Function GetNthCombination(N As Long, r As Long, ByVal CombinationNo As LongLong) As Long()

    Dim Combination() As Long, i As Long, j As Long
    Dim Temp As LongLong
    ReDim Combination(1 To r)
   
    j = 0
    For i = 1 To r
        Do
            j = j + 1
            Temp = Round(Application.Combin(N - j, r - i), 0)
            If CombinationNo <= Temp Then
                Combination(i) = j
                Exit Do
            End If
            CombinationNo = CombinationNo - Temp
        Loop
    Next i
   
    GetNthCombination = Combination

End Function
Function GetIndexNumber(N As Long, r As Long, Vector As Variant) As LongLong

    Dim Combination As Variant
    Dim i As Long
    Dim Total As LongLong
   
    Combination = Vector
    If TypeOf Vector Is Range Then
        Combination = Application.Transpose(Vector)
        If Vector.Columns.Count > 1 Then Combination = Application.Transpose(Combination)
    End If
    Total = Application.Combin(N, r)
   
    On Error Resume Next
    For i = LBound(Combination) To UBound(Combination)
        Total = Total - Application.Combin(N - Combination(i), r + LBound(Combination) - i)
    Next i
    On Error GoTo 0
   
    GetIndexNumber = Total

End Function