vba Pattern of numbers using for next.

[montecarlo2012]

Hello people.

Sub April_16()
   For i = 1 To 5
         For j = 1 To i
               Cells(i, j).Value = j
               
                  For k = i + 1 To j
                            Cells(i, j).Value = 1
                       
                  Next
         Next
   Next
End Sub

with this code the output is:

It is not, what I expected
This is what is
my goal

Thank you for reading this
Please, anyone give me a hand here.

 

[FormR]

Hi, maybe..

Sub April_16()
Dim i As Long, j As Long
For i = 1 To 5
    For j = 1 To 5
          Cells(i, j).Value = IIf(j > i, 1, j)
    Next j
Next i
End Sub

 

[Marc L]

Hi, as it can be achieved without looping … Anyway for the looping fun :​

Sub Demo1()
  Const D = ","
    For C% = 1 To 5
        [A:E].Rows(C).Value2 = Split(Join(Evaluate("COLUMN(" & Cells(1).Resize(, C).Address & ")"), D) & Application.Rept(D & "1", 5 - C), D)
    Next
End Sub

 

[Marc L]

Not a concern here with such few columns but with big data the smart way is to use an array variable​

in order to just write data at once rather than cell by cell aka columns # * columns # (here 5 * 5 = 25 times but imagine for 16 384 columns !)​

and obviouly smarter using zero rather than one :​

Sub Demo2()
    Dim R%, C%, N%(1 To 5, 1 To 5)
    For R = 1 To 5
    For C = 1 To R
        N(R, C) = C
    Next C, R
    [A1:E5].Value2 = N
End Sub

 

[montecarlo2012]

Thanks Both.