Excel Auto Complete Cells until given value

[stefanfreund]

Hello friends,

I have a question that is bothering me for some time now, and it is a bit difficult to explain, hope you will understand.

I have a sheet that looks like the table below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]1
[/TD]
[TD]7
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]8
[/TD]
[TD]14
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]9
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]10
[/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]11
[/TD]
[TD]17
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]12
[/TD]
[TD]18
[/TD]
[/TR]
</tbody>[/TABLE]

Now, I am trying to find out a way (formula, vba or some kind of solution) that I tell excel exactly till what number to go down the column, and then it continues to the other column. So for example in the table above there are 6 data inputs per column, but if for example my data set changes and I need to have 12 per column, then I would like that I just tell excel to automatically go like 1,2,3,4,5,6,7,8,9,10,11,12 and then continue with 13,14,15.. on the other column (yes, I want to have consecutive numbers from 1 until lets say 1000). I know it sounds a bit complicated or confusing so please let me know if something is not clear.

Thank you very much in advance for your help!
You are the best

 

[MickG]

Try this:-
NB:- Change Constant "NumPC" as required.

[COLOR="Navy"]Sub[/COLOR] MG23Feb57
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Const NumPC = 12 '[COLOR="Green"][B] New column when 12th row is filled[/B][/COLOR]
ac = 1
[COLOR="Navy"]For[/COLOR] n = 1 To 1000
    c = c + 1
    [COLOR="Navy"]If[/COLOR] c = 13 Then: ac = ac + 1: c = 1
    Cells(c, ac) = n
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[My Aswer Is This]

Try this:

Sub Test()
'Modified 2/23/17 8:15 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim r As Long
r = 1
b = 1
Dim ans As Long
ans = InputBox("How many rows per column")
    For i = 1 To 1000
        Cells(r, b).Value = i
        r = r + 1
        If r = ans + 1 Then r = 1: b = b + 1
        
    Next
Application.ScreenUpdating = True
End Sub

 

[stefanfreund]

Fantastic! Thank you very much Mick!

Try this:-
NB:- Change Constant "NumPC" as required.

[COLOR=Navy]Sub[/COLOR] MG23Feb57
[COLOR=Navy]Dim[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Const NumPC = 12 '[COLOR=Green][B] New column when 12th row is filled[/B][/COLOR]
ac = 1
[COLOR=Navy]For[/COLOR] n = 1 To 1000
    c = c + 1
    [COLOR=Navy]If[/COLOR] c = 13 Then: ac = ac + 1: c = 1
    Cells(c, ac) = n
[COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

You're welcome

NB:- I think "My Answer is this" has code that is probably more useful as it has an InputBox

 

[FormR]

(formula, vba or some kind of solution)

Hi, here is a formula way if interested - in this example A1 contains the number of rows to populate and the formula in A3 can be copied down and across as far as may be required.

Excel 2013
	A	B	C	D	E	F
1	6
2
3	1	7	13	19	25	31
4	2	8	14	20	26	32
5	3	9	15	21	27	33
6	4	10	16	22	28	34
7	5	11	17	23	29	35
8	6	12	18	24	30	36
Sheet2

Cell Formulas
Range	Formula
A3	=IF(ROWS(A$3:A3)>$A$1,"",ROWS(A$3:A3)+(COLUMNS($A3:A3)*$A$1)-$A$1)

 

[stefanfreund]

This is also very useful. Thank you!

 

[stefanfreund]

oh, great! Thank you!

Try this:

Sub Test()
'Modified 2/23/17 8:15 AM EST
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim r As Long
r = 1
b = 1
Dim ans As Long
ans = InputBox("How many rows per column")
    For i = 1 To 1000
        Cells(r, b).Value = i
        r = r + 1
        If r = ans + 1 Then r = 1: b = b + 1
        
    Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

oh, great! Thank you!

 

[Ingolf]

Try this:-
NB:- Change Constant "NumPC" as required.

[COLOR=navy]Sub[/COLOR] MG23Feb57
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Const NumPC = 12 '[COLOR=green][B] New column when 12th row is filled[/B][/COLOR]
ac = 1
[COLOR=navy]For[/COLOR] n = 1 To 1000
    c = c + 1
    [COLOR=navy]If[/COLOR] c = 13 Then: ac = ac + 1: c = 1
    Cells(c, ac) = n
[COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Hi,

New to VBA ant try to understand.
What is the role of this line?

Const NumPC = 12 '[COLOR=green][B] New column when 12th row is filled[/B][/COLOR]