Imran has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Excel Advanced - Formulas & Functions course
Excel Advanced - For Power Users course
VBA SUM a Range
Hi,
I am stuck on some VBA code that I am doing. I need to calculate a mean for a set of data. I need to sum the data in 259 cells and then divide by 259 cells to get an average.
What i need to know is how do I add a counter to count the cells going down from the first blank row and add up all the cells in that area. I have come up with the following code, but can't seem to get a counter going. I can't use an exact cell ref like A259 because the cells keep changing as more data is added and rows inserted.
Function StdDev(mean As Single)
StdDev = ActiveCell.Offset(1, 0) - mean
End Function
Sub StandardDeviation()
Dim CumlValues As Long
Range("r10").Select
Do Until ActiveCell <> 0
If ActiveCell = 0 Then
ActiveCell.Offset(1, 0).Select
End If
Loop
Do Until
CumlValues = CumlValues + ActiveCell.Offset(1, 0).Value
Loop
Range("r5") = CumlValues
End Sub
RE: VBA SUM a Range
Hi Imran
Thanks for getting in touch. If you're thinking about counting, have a look at the exercise we did on the course titled "Check Orders". We used variables to keep a running total of counted cells.
As an alternative, let's not forget about all those functions already exist in Excel and could save you a lot of time.
e.g. Application.WorksheetFunction.StDev(range("b4:B10"))
Application.WorksheetFunction.Average(range("b4:B10"))
Or if you wanted something that started from a particular cell all the way down to the end of the data you could use:
Application.WorksheetFunction.StDev(Range("b4", range("b4").End(xlDown)))
Application.WorksheetFunction.Average(Range("b4", range("b4").End(xlDown)))
Kind regards
Gary Fenn
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: VBA SUM a Range
Hi Gary,
Thanks for your quick response. I will take a look at that.
For the worksheet functions, can I use offset instead of range, so
Application.WorksheetFunction.StDev(range("b4:B10"))
is now
Application.WorksheetFunction.StDev(range("offset(1,0):offset(259,0)"))
or is this the wrong syntax? The reason why I ask is that the cells will not always be, for example, b4:b10,
Regards
Imran
RE: VBA SUM a Range
Hi Imran
Thanks for your reply. Yes you're in the right ballpark, but OFFSET still needs something to start from.
e.g.
Application.WorksheetFunction.Average(Range(activecell, activecell.Offset(259,0)))
Kind regards
Gary Fenn
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: VBA SUM a Range
Hi Gary,
I do remember a little of the CheckOrders, but I don't think I got the correct code on it. This gives me a zero value in the msgbox.
Sub Parttwo()
Dim TotalValue As Long
Dim TotalOrders As Long
Dim Date_Count As Long
Dim Date_single As Long
Range("f2").Select
Date_single = 1
Do Until ActiveCell.Offset(0, -5) = ""
If ActiveCell = vbRed Then
Date_Count = Date_single + Date_Count
End If
ActiveCell.Offset(1, 0).Select
Loop
MsgBox Date_Count
End Sub
RE: VBA SUM a Range
Hi
So close! You should change:
If ActiveCell = vbRed Then
to
If ActiveCell.Font.Color = vbRed Then
Kind regards
Gary Fenn
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
