Average in VBA in Excel

Question

I am new to VBA in excel. I have a range of data with 11 000 numbers. I want to calculate the average of first 60, then next 60 until the end. I did some programming but it isn't working. Can someone please help me with it?

Sub Hourlyaverage()
    Dim i As Long, j As Long, k As Long, l  As Long, m As Long
    
    Sheets("DUT1_Test51_excel").Select
    
    i = 3
    j = 3
    k = 63
        
    Do While Cells(i, 12).Value <> ""
        
        l = Cells(i, 12).Value
        m = Cells(k, 12).Value
        
        Cells(j, 20).Value = [Average (l : m)]
        i = i + 60
        j = j + 1
        k = k + 60
    Loop
    
End Sub

314UnreadEmails · Accepted Answer · 2021-12-06 17:17:53Z

5

Look closely at what you're trying to do in this code:

l = Cells(i, 12).Value
m = Cells(k, 12).Value

Cells(j, 20).Value = [Average (l : m)]

You're assigning a "long" value to each of l and m and then calling them in the average function as if they were references to a cell.

I suggest that you add a range variable, assign the location of the data you want to average and use that as your function argument.

Something like:

Dim myRange as Range

'Loop code here
Set myRange = Range("L" & i & ":L" & k)
Cells(j, 20).Value = Application.WorksheetFunction.Average(myRange)

edited Dec 6, 2021 at 17:17

answered Apr 22, 2015 at 15:55

314UnreadEmails

3191 silver badge9 bronze badges

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

2 Comments

mrduncle1 Over a year ago

Should the last line be: Cells(j, 20).Value = Application.WorkSheetFunction.Average(myRange)

314UnreadEmails Over a year ago

Yep, edited in the answer. Thanks for catching it.

Collectives™ on Stack Overflow

Average in VBA in Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related