VBA - Using Average function in Sheets.Range

Question

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

Error 1004 · Accepted Answer · 2019-03-28 17:44:58Z

2

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"))

answered Mar 28, 2019 at 17:44

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

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

2 Comments

VBAWARD Over a year ago

worked like a charm - thank you! the application.worksheetfunction is needed to get this to work?

Error 1004 Over a year ago

i prefer to use for complete reference.

Collectives™ on Stack Overflow

VBA - Using Average function in Sheets.Range

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