combination

[farasatbalooch]

i want to make numbers combination from 1 to 49
5 numbers combination for example ( 1 2 3 4 5 ) can you
tell me the code it will be a graet help i am using
MS EXCEL 2013.

thanks.

 

[rondeondo]

there is a function, RAND(), that generates a random number between 0 and 1. To make a number between 1 and 49, add 1 and multiply by 48, so =1+RAND()*48. Then if you want 5 numbers, repeat 5 times then concatenate the results. or build all in one cell. add TRUNC() to make the result an integer In later versions there's an INT() function for this:
="("&TRUNC(1+RAND()*48,0)&" "&TRUNC(1+RAND()*48,0)&" "&TRUNC(1+RAND()*48,0)&" "&TRUNC(1+RAND()*48,0)&" "&TRUNC(1+RAND()*48,0)&" "&" )"

 

[farasatbalooch]

there is a function, RAND(), that generates a random number between 0 and 1. To make a number between 1 and 49, add 1 and multiply by 48, so =1+RAND()*48. Then if you want 5 numbers, repeat 5 times then concatenate the results. or build all in one cell. add TRUNC() to make the result an integer In later versions there's an INT() function for this:
="("&TRUNC(1+RAND()*48,0)&" "&TRUNC(1+RAND()*48,0)&" "&TRUNC(1+RAND()*48,0)&" "&TRUNC(1+RAND()*48,0)&" "&TRUNC(1+RAND()*48,0)&" "&

Sub Maja ()
Dim K As Long
K = 1
vals = Array ("3", "6", "9")
For Each a In vals
For Each b In vals
For Each c In vals
For Each d In vals
For Each e In vals
Cells (K, 1) = a & b & c & d & e
K = K + 1
Next e
Next d
Next c
Next b
Next a
MsgBox K
End Sub

i am runing this code in excel vba but its giving me error 1004 can you please tell me how to add columes in this code so it will give me no errors

 

[Joe4]

i am runing this code in excel vba but its giving me error 1004 can you please tell me how to add columes in this code so it will give me no errors

Your code runs just fine for me.
I do not see anything in the code that would cause that error.

Is that really the size of array and number of loops you actually have, or have you simplified it?
It is important that we see your EXACT code, as-is, in order to see what the problem might be.

 

[Fluff]

@farasatbalooch please stop posting your replies inside quotes, especially when it appears you are quoting somebody else.

 

[farasatbalooch]

Your code runs just fine for me.
I do not see anything in the code that would cause that error.

Is that really the size of array and number of loops you actually have, or have you simplified it?
It is important that we see your EXACT code, as-is, in order to see what the problem might be.

I was making a combination of 5 numbers from 1 to 49 numbers and I just edit this code for my self and the exact count of my combination is around 20 million combination and excel only hai 10 million rows so I want divide the combination in 2 or 3 rows of you can help me how can I add row B and C in this code it will be a great help thanks

 

[Joe4]

I was making a combination of 5 numbers from 1 to 49 numbers and I just edit this code for my self and the exact count of my combination is around 20 million combination and excel only hai 10 million rows so I want divide the combination in 2 or 3 rows of you can help me how can I add row B and C in this code it will be a great help thanks

You shouldn't oversimply your code/question like that. It makes it harder to debug the errors when the code you post does not create any errors.

You could try something like this, though I am not sure how long it will take it to run (it could take a while):

Sub Maja2()

    Dim K As Long
    Dim cl As Long
    Dim rw As Long
    
    cl = 1
    rw = 1
    
    vals = Array("3", "6", "9")

    Application.ScreenUpdating = False
    
    For Each a In vals
        For Each b In vals
            For Each c In vals
                For Each d In vals
                    For Each e In vals
                        Cells(rw, cl) = a & b & c & d & e
                        If rw = Rows.Count Then
                            rw = 1
                            cl = cl + 1
                        Else
                            rw = rw + 1
                        End If
                    Next e
                Next d
            Next c
        Next b
    Next a
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro complete"
    
End Sub

 

[farasatbalooch]

You shouldn't oversimply your code/question like that. It makes it harder to debug the errors when the code you post does not create any errors.

You could try something like this, though I am not sure how long it will take it to run (it could take a while):

Sub Maja2()

    Dim K As Long
    Dim cl As Long
    Dim rw As Long
   
    cl = 1
    rw = 1
   
    vals = Array("3", "6", "9")

    Application.ScreenUpdating = False
   
    For Each a In vals
        For Each b In vals
            For Each c In vals
                For Each d In vals
                    For Each e In vals
                        Cells(rw, cl) = a & b & c & d & e
                        If rw = Rows.Count Then
                            rw = 1
                            cl = cl + 1
                        Else
                            rw = rw + 1
                        End If
                    Next e
                Next d
            Next c
        Next b
    Next a
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete"
   
End Sub

Sub Maja()
Dim K As Long
K = 1
vals = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49")
For Each a In vals
For Each b In vals
For Each c In vals
For Each d In vals
For Each e In vals
Cells(K, 1) = a & b & c & d & e
K = K + 1
Next e
Next d
Next c
Next b
Next a
MsgBox K
End Sub



This the code I am runing

 

[Joe4]

Did you try inserting your array into the code I gave you in the previous post?

 

[farasatbalooch]

No I don't know how to make changes in a code sir because I just copy this code from a website and I don't know how to make VBA codes.