Fill an input function to the right dynamically

user_8770088 0
Viewed 172

I have a table of values, lets say:

enter image description here

And I want to achieve this:

enter image description here

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.

vba excel
1 Answers

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
user_18211370
Related