CODEWITHSUNDEEP
excelvbafunctionrangeaverage
VBAWARD
VBAWARD

Reputation: 71

VBA - Using Average function in Sheets.Range

I've tried searching a few different threads but wasn't able to get a solution.

My macro is going to transfer data from one sheet to another. I need to use the AVERAGE function to get the average of a range (on the other sheet)

I'm getting a RUN TIME ERROR 438 error and can't figure out why. I tried using Application.WorksheetFunction but this doesn't bear the correct result and works on the MAX function but not the AVERAGE. I saw a few solutions that involve creating a loop and variables, but I assumed a simple solution should be possible.

Code below:

Option Explicit            

Sub Step8CopytoLean()
    Dim wblean As Workbook
    Dim wbmaster As Workbook

    Set wblean = Workbooks("SLA Reporting Lean.xlsx")
    Set wbmaster = Workbooks("SLA Reporting MasterFile.xlsx")

    Workbooks("Lean.xlsx").Activate
    Worksheets("Data").Delete

    Workbooks("MasterFile.xlsx").Activate
    Worksheets("Data").Copy After:=Workbooks("Lean.xlsx").Sheets("Summary")

    Workbooks("Lean.xlsx").Sheets("Summary").Activate

    wblean.Sheets("Summary").Range("E4").Value = wbmaster.Sheets("Summary").Range("K20") 
    wblean.Sheets("Summary").Range("F4").Value = wbmaster.Sheets("Summary").Range("M20") 
    wblean.Sheets("Summary").Range("E5:E6").Value = wbmaster.Sheets("Summary").Average(Range("K9:K11")) 'line with error
    wblean.Sheets("Summary").Range("F5").Value = wbmaster.Sheets("Summary").Max(Range("M8:M11"))  'line with error

Upvotes: 0

Views: 1199

Answers (1)

Error 1004
Error 1004

Reputation: 8220

Modify the below and try:

Application.WorksheetFunction.Average(ThisWorkbook.Worksheets("Sheet1").Range("A1:A10"))

Something like this:

wblean.Sheets("Summary").Range("E5:E6").Value = Application.WorksheetFunction.Average(wbmaster.Worksheets("Summary").Range("K9:K11"))
wblean.Sheets("Summary").Range("F5").Value = Application.WorksheetFunction.Max(wbmaster.Worksheets("Summary").Range("M8:M11"))

Upvotes: 2

Related Questions