vba for next

[montecarlo2012]

Hello People.

Sub Monte_Carlo()

Dim i As Integer

         For i = 2 To 16
                 Cells(i, 1) = i + Range("A1").Value - 2
         Next

End Sub

This is the result I got, but it is not what I am working on.
Expecting result is:

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

 

[Marc L]

Hi, a demonstration as a beginner starter :​

Sub Demo1()
    [A2:A16].Formula = "=A$1+ROW()-2"
    [B2:E16].FormulaR1C1 = "=RC[-1]+15"
End Sub

Do you like it ? So thanks to click on the bottom right ?Like icon ! ​

 

[Trebor76]

Here's another way:

Option Explicit
Sub Macro1()

    Dim i As Long, j As Long
    
    Application.ScreenUpdating = False
    
    For i = 2 To 16 'Row 2 to 16
        Cells(i, 1).Value = IIf(i = 2, Cells(i, 1).Offset(-1, 0), Cells(i, 1).Offset(-1, 0) + 1)
        For j = 2 To 5 'Columns B to E
            Cells(i, j).Value = Cells(i, j - 1).Value + 16 - 1
        Next j
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

Though as Marc L's solution doesn't loop it is probably the preferred.

Regards,

Robert

 

[Marc L]

According to useless loop but with a single only, just for the fun :​

Sub Demo2()
   Const R = 15
        N& = [A1].Value2
    For C% = 1 To 5
        Cells(2, C).Resize(R).Value2 = Evaluate("ROW(" & N & ":" & N + R - 1 & ")")
        N = N + R
    Next
End Sub

Do you like it ? So thanks to click on the bottom right ?Like icon ! ​

 

[Marc L]

Or just with a single formula :​

Sub Demo01()
    With [A2:E16]:  .Formula = "=$A$1+ROW()-2+(COLUMN()-1)*15":  .Formula = .Value2:  End With
End Sub

Do you like it ? So thanks to click on the bottom right ?Like icon ! ​

 

[Michael M]

Try

Sub Monte_Carlo()
Dim i As Integer, c As Integer
x = Range("A1").Value
For c = 1 To 5
For i = 2 To 16
        Cells(i, c) = x
        x = x + 1
Next i
        Cells(i, c) = x
Next c
End Sub

 

[montecarlo2012]

Thank you Mr. Trebor, your code work for the specific question I post, was my fault I didn't say I was expecting to be able to change the ranges or dimensions, so when I change the parameter do not work.
Thank you again for your time.

Marc L.
Your code is flexible, I mean, let me change the numbers around and make it bigger my array IF I want.
Now, let me ask you, you are new (here, in this forum) but your code is really advance, I am impress
Can you PLEASE, have a little time to give me more insides about the logic behind the code, I mean how from my informal English descriptions you got the "resize" statement, I was expecting a simple loop and you really give me a heavy code, CONGRATULATIONS, you are good.
THANK YOU.

 

[montecarlo2012]

Michael M thank you, but the code repeat the last number, at the beginning of the column, and when I expand the array the same this is your answer how looks like

as you see A17 = B2 etc, it is not expected this way.
Thank you for your time.

 

[Michael M]

SO, if you want a code you understand, why not my loop

Sub Monte_Carlo()
Dim i As Integer, c As Integer
x = Range("A1").Value
For c = 1 To 5
For i = 2 To 16
        Cells(i, c) = x
        x = x + 1
Next i
        Cells(i, c) = x+1
Next c
End Sub

 

[Michael M]

Oops, try this

Sub Monte_Carlo()
Dim i As Integer, c As Integer
x = Range("A1").Value
For c = 1 To 5
For i = 2 To 16
        Cells(i, c) = x
        x = x + 1
Next i
Next c
End Sub