CODEWITHSUNDEEP
vbaexcel
LiamNeesonFan
LiamNeesonFan

Reputation: 2813

VBA Error with Taking the Average of a Set of Scores

I am trying to take the scores of a test from cells 2 to 19 and set that equal to 20. However, when I enter the code it only prints out #NAME?. How can I fix it to make sure it prints the average score?

Sub Scores()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet3")
    ws.Activate
    Dim rngStart As Range
    Dim rngEnd As Range
    Set rngStart = Range("A1").Cells(2, 5)
    Set rngEnd = Range("A1").Cells(19, 5)
    Dim MidtermAvg As Range
    Set MidtermAvg = Range(rngEnd.Offset(1, 0), rngEnd.Offset(1, 0))
    ' Put in average at the bottom of the range
    MidtermAvg.Formula = "=AVERAGE(rngEnd:rngStart)"
End Sub

Here are the scores all lined in one column:

63
44
87
96
67
82
58
71
61
77
63
30
93
38
89
89
93
93

Upvotes: 1

Views: 336

Answers (2)

Alex P
Alex P

Reputation: 12497

I am not sure why you are using Range("A1") and then Cells(r,c) notation to get the range "E2:E19". If the scores are in that range then why not as follows:

Sub AverageScore()
    Dim scores As Range
    Set scores = Range("E2:E" & Range("E2").End(xlDown).Row)
    Range("E" & scores.End(xlDown).Row).Offset(1, 0) = "Avg: " & Round(WorksheetFunction.Average(scores), 2)
End Sub

NB - I have taken the liberty of rounding the average to 2 decimal points and adding a labelso that the result reads Avg: 71.89 but you can change as you see fit.

Upvotes: 1

mechanical_meat
mechanical_meat

Reputation: 169514

You were missing some of the worksheet references,
and your formula had the rngStart and rngEnd variables hiding in a string.
(How would VBA know that those are variables?)

Also, you need to get the .Address of those range objects.
This works:

Sub Scores()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet3")
    ws.Activate
    Dim rngStart As Range
    Dim rngEnd As Range
    Set rngStart = ws.Range("A1").Cells(2, 5)
    Set rngEnd = ws.Range("A1").Cells(19, 5)
    Dim MidtermAvg As Range
    Set MidtermAvg = ws.Range(rngEnd.Offset(1, 0), rngEnd.Offset(1, 0))
    ' Put in average at the bottom of the range
    MidtermAvg.Formula = "=AVERAGE(" & rngStart.Address & ":" & rngEnd.Address & ")"
End Sub

Upvotes: 3

Related Questions