CODEWITHSUNDEEP
vbaexcelaverageworksheet-function
rozario_k
rozario_k

Reputation: 59

Average function in macro not returning value

Dim XXXX as string

XXXX = Sheets("Sheet1").Application.WorksheetFunction.Average(Range("J7:J607")).value
Sheets("Log").Cells(2, "Z").value = XXXX

End Function

Please see the above macro. I need the average of the values available in J column and need to paste that average value in "Log" sheet. But the value is not returning, instead leaving it as a blank cell. But the code runs fine, not showing any error.

Please help me to fix this issue guys.

Upvotes: 0

Views: 432

Answers (2)

CLR
CLR

Reputation: 12254

The following would work in place of your current code:

Dim XXXX As String

XXXX = Application.WorksheetFunction.Average(Range("J7:J607"))
Sheets("Log").Cells(2, "Z").Value = XXXX

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149277

You are trying to write to another cell in a Function and then I am guessing that you are calling that from a worksheet?

Use a Sub instead. Of Course you cant call this from a worksheet.

Try this

Private Sub Sample()
    Dim XXXX As String

    With Sheets("Sheet1")
        XXXX = Application.WorksheetFunction.Average(.Range("J7:J607"))
        Sheets("Log").Cells(2, "Z").Value = XXXX
    End With
End Sub

If you want to use a function and call it from a worksheet then use it like this

Private Function GetAvg()
    GetAvg = Application.WorksheetFunction.Average(Sheets("Sheet1").Range("J7:J607"))
End Function

and then in cell Z2 Type this

=GetAvg()

Upvotes: 1

Related Questions