VBA Help! Can a one cell function returning outputs to multiple cells?

[SurfsUp123]

Hi there,
Can anyone tell me if it is possible to make a function in one cell output to multiple cells, descending vertically.

Here is a simple example where I copy numbers from one column to another, but the for loop just loops it to the bottom of the data set instead of outputting in one cell and then moving down to the next. is it possible to fix this?

Function myOne(data)

    Dim nrows As Integer
    nrows = data.Rows.Count
    
    'defne array
    Dim DataArray() As Double
    ReDim DataArray(1 To nrows, 1)
    
    'store data into array
    For i = 1 To nrows
        DataArray(i, 1) = data.Cells(i, 1)
    Next i
    
    For i = 1 To nrows
    
        myOne = data.Cells(i, 1)
        
    Next i


End Function

 

[RoryA]

Only if you have Microsoft365 with the new dynamic arrays, or you array enter the formula into a suitable number of cells initially.

 

[SurfsUp123]

Only if you have Microsoft365 with the new dynamic arrays, or you array enter the formula into a suitable number of cells initially.

Thanks for the quick response. What do you mean by array enter the formula into the cells?

 

[RoryA]

I mean that you select multiple cells, type the formula then press Ctrl+Shift+Enter rather than just Enter. This assumes that your function returns a correctly oriented array. I'm also assuming that you meant you wanted to enter your myOne function as a formula in the sheet. Your function should return the DataArray variable by the way.