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]

Michael M. Great Answer Very Flexible and very Simple way to do it THANK YOU SO MUCH. I marked as Solution and also I give you a "Like".
Have a great Sunday.

 

[Michael M]

Glad WE could help

 

[Marc L]

more insides about the logic behind the code, I mean how from my informal English descriptions you got the "resize" statement

Looping is useless just using Excel basics at beginner level and I got the statement from the VBA help.​

If for some reason a loop is really necessary the logic is to directly use columns of rows # …​

​

 

[montecarlo2012]

wow. how is that possible....................???????

......................Really & necessary ....interesting
directly use columns of rows --------- directly use.......(how indirectly will be). ...
spreadsheet automatically pop up in my brain columns and rows or vice versa, so how loops are useless, interesting if you are willing to go farther.
Thanks

 

[Marc L]

​

Yes looping is 'useless' when some Excel basics features can already do the job like any Excel beginner operating manually​

and often it's faster than a bad looping code on big / huge data …​

​

One day I saw an Excel beginner kid - a summer job - operating manually just with Excel basics in no more than a minute​

but a bad looping VBA procedure for the same was lasting for ages …​

 

[Marc L]

directly use.......(how indirectly will be)

Rather than a code using two loops for example …​

 

[montecarlo2012]

Thank you Marc L, is good to have a ninja here.

 

[Marc L]

Thanks. It was just a reminder : thinking first about the Excel side before VBA may avoid some gas factory code …​

​

As a little example only two codelines are necessary to reach the goal of this thread :​

VBA Show only rows with FALSE value

Dear VBA Masters I want below code to check range and If any row contains value FALSE then EntireRow.Hidden = False , else I want rest of rows to be hidden. Seems simple but it does not work the way I want. Below code shows only 2 rows from below example. Why ? Sub ShowFalseOnly() Dim...

www.mrexcel.com

 

[Peter_SSs]

Looping is useless

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

As a little example only two codelines are necessary to reach the goal of this thread :

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.

 

[montecarlo2012]

Mr. Marc L, I really appreciate your kindness, and your time. I am curious, You mention two lines solution, ►where are they◄
I see you in another forum from the other side of the world, you started on apr. so let me tell you from me, welcome on board, and, this is a real good friendly people, all levels, are welcome, there are a great, great people here, very open and helpful, the best part here in this forum, the people explain and understand we all are different,
ok Mr. Mark thanks.