Fill an input function to the right dynamically

Question

I have a table of values, lets say:

And I want to achieve this:

the number of rows I manually select, but the columns could vary, and I input the function and number of columns into a another function, in this case =SUM(A1:A5) and 4 for the function below:

Sub FunctionFill()

    Dim myFunction As String: myFunction = "=SUM(A1:A5)"
    Dim Numvendors As Integer: Numvendors = 4
    Cells(Application.ActiveCell.Row, Application.ActiveCell.Column).Resize(, Numvendors).Formula = myFunction
    
End Sub

This works as a macro.

How do I change this sub into a function that takes Numvendors and myFunction as inputs and outputs the range.

Answer 1

Try this...

You don't really need range as well as cells. You can fill all the cells at once using Resize rather than using a loop

.Formula tells it you want to set the cell formula.

Function FunctionFill(myFunction As String, NumVendors As Integer)

    Cells(Application.ActiveCell.Row, Application.ActiveCell.Column).Resize(, NumVendors + 1).Formula = myFunction

End Function