Imran has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - For Power Users course
Sum an Array
Hi,
I want to sum an array however I keep getting a zero in cell D2.
Sub Array_Sum()
Dim SalesData(1 To 7)
Range("b1").Select
For i = 1 To 7
SalesData(i) = Application.WorksheetFunction.Sum(SalesData)
Range("d2").Value = SalesData(i)
Next
End Sub
RE: Sum an Array
Hi Imran,
I am not sure that I understand what you try to do.
You create an array where you can store 7 values. The for next loop will run 7 times but what do you want to store in the array?
The line below will not store anything in the array. It will also if it was a value put the same information in the array seven times from SalesData 1 to 7:
Application.WorksheetFunction.Sum(SalesData)
Sub Array_Sum()
Dim SalesData(1 To 7)
Range("b1").Select
For i = 1 To 7
SalesData(i) = Application.WorksheetFunction.Sum(SalesData)
the line below will overwrite D2 seven times with the value you have stored in the array SalesData 1 to 7
Range("d2").Value = SalesData(i)
Next
End Sub
What do you have in B1
Could you please explain which data you want to store in the array. Is it from 7 different cells.
If you just want to summarize 7 cells you will not need an array.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Sum an Array
Hi Jens,
I have 7 numbers in cells B2 to B8, and I want to Sum these up and enter them into one cell (D2).
RE: Sum an Array
Hi Imran,
The easiest way of doing it is:
Range("d2").value="=sum(b2:b8)"
If you want to use an array, you can do it like this:
Sub TestArray()
Dim dTotal As Double
Dim SalesData(1 To 7) As Double
Dim i As Integer
For i = 1 To 7
SalesData(i) = Cells(i + 1, 2).Value
dTotal = dTotal + SalesData(i)
Next i
Range("d2").Value = dTotal
End Sub
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
