excel: is it possible to put a FOR statement as a formula within a cell?

Question

can i do this =(For i=1 to 100, print i)

is there a way to put a FOR statement inside a cell WITHOUT USING VBA?

Answer 1

The VBA for this isn't fancy at all :-)

Option Explicit

Sub SimpleForLoop()
    Dim i As Integer

    For i = 1 To 100 Step 1
        With ActiveWorkbook.Sheets(1).Cells(1, 1)
            .Value = .Value + i
        End With        
    Next
End Sub

The simple code above puts the value 5050 in cell A1.

If you want to concatenate a string instead, slap this code into your for-loop:

    With ActiveWorkbook.Sheets(1).Cells(2, 1)
        If .Value = "" Then
            .Value = CStr(i)
        Else
            .Value = .Value & "," & CStr(i)
        End If
    End With

Which will print the following into cell A2:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100

Answer 2

You can use an array to get the numbers 1 through 100, but you're limited on what you can do with them. You can't, for instance, concatenate in an array formula (which your pseudo code suggests). But you could SUM, AVERAGE or many other operations.

{=SUM(ROW(1:100))}
{=AVERAGE(ROW(1:100))}
{=MAX(ROW(1:100))}

The braces means enter with control+shift+enter, not just enter.