Vba loop sequence

[Frankroger]

Can anyone please guide me with this problem

I'm trying to create an number square, but want to use vba rather than formulas

I want to be able to set the size of the square, if the number 3 is entered, it would be 3x3,
3 rows and 3 columns
If 4 was entered, then it would be 4x4, 4 rows by 4 columns etc.

Then for it to begin from an selected number, in the example I've set it to start at 10, and to be able to increment by an selected value.

I've put two examples using formula, but I don't understand vba well enough

So far all I have is the first column figured with vba =(

Dim i As Integer

i = 5

For i = 1 To i

Activecell.value = 1
Activecell.offset(1, 0).activate

Next i

End sub

Book1
	J	K	L	M	N	O	P	Q	R	S	T	U	V
12
13								50	100	150	200	250
14								40	90	140	190	240
15		3	6	9				30	80	130	180	230
16		2	5	8				20	70	120	170	220
17		1	4	7				10	60	110	160	210
18
19	Start	1					Start	10
20	increment	1					increment	10
21	Size	3					Size	5
22
23
Sheet1

Cell Formulas
Range	Formula
Q13:U16	Q13	=Q14+$Q$20
Q17,K17	Q17	=Q19
R17:U17	R17	=Q13+$Q$20
K15:K16	K15	=K16+K19
L15	L15	=L16+K20
M15	M15	=M16+K20
L16	L16	=L17+K20
M16	M16	=M17+K20
L17	L17	=K15+K20
M17	M17	=L15+K20

 

[6StringJazzer]

Modify code as needed for where your parameters are located and where you want the square to start:

Public Sub NumberSquare()

   Dim Start As Long
   Dim Increment As Long
   Dim Size As Long
   Dim Row As Long, Column As Long
   Dim n As Long
 
   Start = [B1]
   Increment = [B2]
   Size = [B3]
 
   n = Start
   For Column = 3 To Size + 3 - 1 ' First column is 3=C
      For Row = 1 + Size - 1 To 1 Step -1 ' First row is 1
         Cells(Row, Column) = n
         n = n + Increment
      Next Row
   Next Column

End Sub

$scratch.xlsm
	A	B	C	D	E
1	Start	1	5	11	17
2	Increment	2	3	9	15
3	Size	3	1	7	13
Number Square

 

[Frankroger]

Modify code as needed for where your parameters are located and where you want the square to start:

Public Sub NumberSquare()

   Dim Start As Long
   Dim Increment As Long
   Dim Size As Long
   Dim Row As Long, Column As Long
   Dim n As Long
 
   Start = [B1]
   Increment = [B2]
   Size = [B3]
 
   n = Start
   For Column = 3 To Size + 3 - 1 ' First column is 3=C
      For Row = 1 + Size - 1 To 1 Step -1 ' First row is 1
         Cells(Row, Column) = n
         n = n + Increment
      Next Row
   Next Column

End Sub

$scratch.xlsm
	A	B	C	D	E
1	Start	1	5	11	17
2	Increment	2	3	9	15
3	Size	3	1	7	13
Number Square

Thank you so much, it works great!

Is it possible to add increments that are less than 1,

If I set the increment to 0.1, its returning 1s for all cells

 

[6StringJazzer]

Change the declaration for Increment

Dim Increment As Double

You will also need to do the same with Start if you want to start with 0.1 or some other real number

 

[Frankroger]

Change the declaration for Increment

Dim Increment As Double

You will also need to do the same with Start if you want to start with 0.1 or some other real number

Thank you for the help! =)

May I ask you a different variation of number square on a new thread?

It's the same number sequences again, only spiraling out from a centre, rather than stacked in columns.
I can explain it better with an example in a new thread