98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » VBA SUM a Range
VBA SUM a Range
Resolved · Medium Priority · Version 2007
Mon 23 Dec 2013, 14:15Delegate Imran said...
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
Mon 23 Dec 2013, 14:36Trainer Gary said...
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
Mon 23 Dec 2013, 15:12Delegate Imran said...
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
Mon 23 Dec 2013, 15:20Trainer Gary said...
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
Mon 23 Dec 2013, 15:35Delegate Imran said...
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
Mon 23 Dec 2013, 15:42Trainer Gary said...
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
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
