Array of Ranges to work out average value PROBLEM!

I'm trying to create an array of ranges to use with the average function, I would like each range to be processed and the average value found to be printed. I'm getting error 1004 "unable to get average property of worksheet function"

Private Sub CommandButton3_Click()
    Dim Ranges(1 To 7) As String
    Dim i As Long
    Dim RngAvrg1 As Long
      
Ranges(1) = "C:C"
Ranges(2) = "D:D"
Ranges(3) = "E:E"
Ranges(4) = "F:F"
Ranges(5) = "G:G"
Ranges(6) = "H:H"
Ranges(7) = "I:I"

i = 1

Do Until i = 7

RngAvrg1 = Application.WorksheetFunction.Average(Worksheets("Task_Data").Range(Ranges(i)))
        Sheets("Task_Data").Range("L" & i).Value = RngAvrg1
        
i = i + 1

Loop
 
    
    
End Sub

Maybe:

Private Sub CommandButton3_Click()
    Dim Ranges(1 To 7) As String
    Dim i As Long
    Dim RngAvrg1 As Long
      
Ranges(1) = "C:C"
Ranges(2) = "D:D"
Ranges(3) = "E:E"
Ranges(4) = "F:F"
Ranges(5) = "G:G"
Ranges(6) = "H:H"
Ranges(7) = "I:I"

For i = 1 To 7

       RngAvrg1 = Application.WorksheetFunction.Average(Worksheets("Task_Data").Range(Columns(i)))
       Sheets("Task_Data").Range("L" & i).Value = RngAvrg1

Next 
 
End Sub

Regards, TMS

From my testing, if there are no numeric cells in any of those columns AVERAGE will return the #DIV/0 error if in a cell.
In your use, with VBA, this results in the "Unable to get Average property" error message.
Application.Worksheet.Average will result in a run time error.
Application.Average will return an error value (but will then crash because RngAvrg1 is data type Long which is a type mismatch with the data type Error that it will return)

If you declare RngAvrg1 as Variant and use Application.Average rather than Application.Worksheet.Average that should fix things.

Private Sub CommandButton3_Click()
    Dim Ranges(1 To 7) As String
    Dim i As Long
    Dim RngAvrg1 As Variant: ' <<<<<<<<<<
      
    Ranges(1) = "C:C"
    Ranges(2) = "D:D"
    Ranges(3) = "E:E"
    Ranges(4) = "F:F"
    Ranges(5) = "G:G"
    Ranges(6) = "H:H"
    Ranges(7) = "I:I"
    
    i = 1
    
    Do Until i = 7
        RngAvrg1 = Application.Average(Worksheets("Task_Data").Range(Ranges(i))) ' <<<<<<<
        Sheets("Task_Data").Range("L" & i).Value = RngAvrg1
        i = i + 1
    Loop
 
End Sub

or you could replace the whole thing with

With Sheets("Task_Data").Range("L1:L7")
    .FormulaR1C1 = "=AVERAGE(INDEX(C3:C9, 0, ROW()))"
    '.value = .value
End With

|

I tried your code

With Sheets("Task_Data").Range("L1:L7")
    .FormulaR1C1 = "=AVERAGE(INDEX(C3:C9, 0, ROW()))"
    '.value = .value
End With

It does the job. Can you explain how this works? for future reference

**** EDIT

Both solutions solved the problem. Thank you.

It puts a formula in each cell. The cute part isn't the use of VBA, but using INDEX with the row argument set to 0 to return a whole column of the range of the array.

INDEX(anArray, 0, x) returns the x'th column of anArray
INDEX(anArray, y, 0 ) returns the y'th row of anArray.