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.

 

[montecarlo2012]

I disagree. Looping can be very useful in some circumstances.
Here the OP has indicated (post #7) that the actual range is bigger than the example in post #1 so I tested with 150 rows and 50 columns and expanded the code from post #5 to that range.
Over a series of runs the average run time on my machine was 0.062 seconds
With the following double loop code the same results were achieved in an average of 0.022 seconds. That is, in about 35% of the time.

Sub Demo02()
  Dim a(1 To 150, 1 To 50) As Long
  Dim c As Long, r As Long, currval As Long

  currval = Range("A1").Value
  For c = 1 To UBound(a, 2)
    For r = 1 To UBound(a)
      a(r, c) = currval
      currval = currval + 1
    Next r
  Next c
  Range("A2").Resize(UBound(a), UBound(a, 2)).Value = a
End Sub

BTW, the non-looping version from post #5 could be made more than twice as fast (by my testing on this larger range) as follows - but still not as fast as the looping code above.

Sub Demo03()
    With [A2:AX151]:  .Value = Evaluate("A1+ROW(" & .Address & ")-2+(COLUMN(" & .Address & ")-1)*150"):  End With
End Sub

How is that an example when you have not provided those code lines?
In any case, I don't think the value of any code should be measured by how many lines it takes.

Agree.

 

[montecarlo2012]

Peter_SSs Thank you so much, your code work perfect, beside use an array, great. big lesson for me, You are great teacher.

 

[Peter_SSs]

You're welcome. Glad to contribute. Thanks for the follow-up.

 

[Marc L]

As I wrote in post #15 'useless' so between quotes …​

As I wrote in post #18, in particular for beginners, it can make a code easier just thinking first about what Excel offers​

rather than following a classic algorithm path …​

Obviously for big data playing with arrays and loops is often the way to go.​

​

But for few data, what is the best code, the faster, the shorter, the smarter, the … ?​

In fact none and all ! As it just depends on the original poster : the best code is the one he can understand and maintain himself !​

And as Excel features can be easily operated so with the help of the Macro Recorder it's easier for some beginners to start to learn VBA …​

​

And like VBA usual coders, I prefer to avoid sometimes a 'lot' of codelines by using just an Excel feature which already does well the job​

like for example the post #18 thread in link just needing two codelines :​

the first codeline must be an Excel formula allocated to a worksheet cell using COUNTIF or MATCH whatever and​

the second - last - codeline is just an advanced filter using this cell in the criteria, that's all folks !​

​

My post in this thread was to make the original poster think how can it be possible to do it manually and to code it just with 2 codelines​

but he did not ask for and as the 'MrExcel MVP' must know this classic Excel way but continue on his own​

so I won't waste any time following « Solution belongs to well readers … »​

Of course if I had been the first to reply I would have posted my bunch of two codelines !​

​

I sent it to a VBA beginner kid as a 'lockdown exercice' (no school during 3 weeks) and he well found this Excel solution​

operating manually - the 'difficult' part for him was the formula - and just using the Macro Recorder​

but he asked me : « Too easy, how can it be possible to ask for that in a forum, are you kidding me ?‼ »​

Forgive him, he's so young.​

​