CODEWITHSUNDEEP
excelworksheet-function
Alex Gordon
Alex Gordon

Reputation: 60841

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

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?

Upvotes: 0

Views: 257

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33165

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.

Upvotes: 1

mechanical_meat
mechanical_meat

Reputation: 169474

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

Upvotes: 0

Related Questions