Apply Formula to Cell Using a VBA defined Array

Question

There are definitely easier ways to achieve what this code is trying to do, but I created a simplified code to show what I am trying to achieve. Basically I want to perform calculations on lots of data and I don't want to have to have to add that to the worksheets.

So basically I need to run formulas like sum, averageifs, etc. in a worksheet through using an Array that I defined in my Macro. At this point I can't figure out how to turn this array, containing 5 values, into a "=sum( )" function. Thanks in advance for your help!

Sub ArraySum()

Dim numbers(5) As Double
Dim E As Double

For I = 1 To 5
    E = Cells(I, 1)
    numbers(I) = Cos(E)
Next I

Range("b1").Formula = "=sum(" & numbers & ")"

End Sub

Tim Williams · Accepted Answer · 2016-08-25 17:07:16Z

2

It's not clear why you'd want to do it this way, but:

Sub ArraySum()

    Dim numbers(1 To 5) '<<<
    Dim E As Double

    For I = 1 To 5
        E = Cells(I, 1)
        numbers(I) = Cos(E)
    Next I

    Range("B1").Formula = "=SUM(" & Join(numbers, ",") & ")"

End Sub

but then you might as well just do:

Range("B1").FormulaArray = "=SUM(COS(A1:A5))"

edited Aug 25, 2016 at 17:07

answered Aug 25, 2016 at 16:46

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Topher Over a year ago

Ya it does seem unclear in my mind as well. My end goal would be achieved if I could just do something like this formula within excel, is there a way to do this? =averageif(cos(i2:i5), ">0", cos(i2:i5))

Tim Williams Over a year ago

What exactly do you mean by "within excel" ? - on a worksheet you can just use the array formula in my second example (use Ctrl+Shift+Enter when entering it on the sheet)

Topher Over a year ago

Ya I mean on a worksheet. Right, I can do your example with the ctrl+shift+enter, but it's not letting me do it for averageif(cos(a1:a5), ">0"). Do you know what the problem could be? Appreciate your help.

Tim Williams Over a year ago

Not sure you can use an array formula with averageif

Tim Williams Over a year ago

Can fake it with something like =SUM((COS(A1:A5)>0)*COS(A1:A5))/SUM((COS(A1:A5)>0)*1)

SandPiper · Accepted Answer · 2016-08-25 16:52:24Z

0

Does it have to be an equation in the worksheet? You could just get the total and put it there:

Sub ArraySum()

    Dim numbers(1 To 5) '<<<
    Dim E As Double

    For I = 1 To 5
        E = Cells(I, 1)
        numbers(I) = Cos(E)
    Next I

    Range("B1").Value = Application.WorksheetFunction.Sum(numbers)
End Sub

answered Aug 25, 2016 at 16:52

SandPiper

2,9145 gold badges38 silver badges59 bronze badges

Comments

arcadeprecinct · Accepted Answer · 2016-08-25 16:55:11Z

0

Another way if you don't need the formula to actually be in that cell:

Range("b1").Value = Application.WorksheetFunction.Sum(numbers)

Edit: If you haven't set Option Base 1 then Dim numbers(3) As Double will create a 4-element array numbers(0),...,numbers(3) since the default is Base 0. I would recommend using Dim numbers(1 to 3) As Double rather than relying on the Base option.

Edit 2: You either have forgotten Dim I in your example or don't use Option Explicit which is highly reccomended!

edited Aug 25, 2016 at 16:55

answered Aug 25, 2016 at 16:49

arcadeprecinct

3,7771 gold badge14 silver badges18 bronze badges

Collectives™ on Stack Overflow

Apply Formula to Cell Using a VBA defined Array

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related