Average function in macro not returning value

Question

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.

Answer 1

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