CODEWITHSUNDEEP
vbaexcel
Yoni W
Yoni W

Reputation: 37

VBA replace range inside function formula with its calculated values

This is my first post here. I tried to look for an answer but could not find one.

My issue is to replace range in a formula to its specific values. To illustrate the issue consider a cell:

=AVERAGE(A1:A10)

I want this cell to show the average arguments for the calculation:

=AVERAGE({1;2;3;4;5;6;7;8;9;10})

This thing is parallel to pressing F9 while the range A1:A10 is highlighted. I tried to build something from what I could find in the net but could not make it working:

Sub ChangeFormulas()
Const CONST_FUNCTION As String = "AVERAGE"
Dim cell As Range
Dim tmp As String
Dim res As Variant

For Each cell In Selection

  With cell

    If InStr(.Formula, CONST_FUNCTION) > 0 Then

        tmp = Mid(.Formula, 10, InStr(.Formula, ")") - (InStr(.Formula, CONST_FUNCTION) + 8))

        cell.Formula = Replace(cell.Formula, tmp, Application.Evaluate(tmp))
    End If
  End With
Next cell
End Sub

Thank you for your help.

Upvotes: 2

Views: 270

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Cant think why, in accordance with @Brandon Barney, but here is what you need

"=AVERAGE({" & join(application.transpose(range("i1:i4").Value),";") & "})"

Which will return like so

=AVERAGE({1;5;5;10})

Upvotes: 1

Related Questions