Get Error/Debug because average's worksheet function

amde10 · Dec 21st 2020

I want to average a range of MSE but I get a debug message "Method 'Range' of Object ' _Global' failed.

these are my code and i've got debug on

MeanMSEMod = Application.WorksheetFunction.Average(Range(MSEMod))

StdDevMSEMod = Application.WorksheetFunction.StDev(Range(MSEMod))

Code

With Worksheets("Modifikasi")
.Range(.Range("DataStartMod").Offset(1, 6), .Range("DataStartMod").Offset(1, 6).End(xlDown)).Name = "MSEMod"
.Range("SumMSEMod").Formula = "=SUM(MSEMod)"
.Range("MonthAvgMod").Formula = "=AVERAGE(MonthFactorsMod)"
.Range("WeekAvgMod").Formula = "=AVERAGE(WeekFactorsMod)"
End With
'Run Solver
SolverReset
SolverOK SetCell:=Worksheets("Modifikasi").Range("SumMSEMod"), MaxMinVal:=2, _
ByChange:=Worksheets("Modifikasi").Range("CFactorMod, MonthFactorsMod, WeekFactorsMod, SFHolMod, SFPerMod, SFRangMod")
SolverAdd CellRef:=Worksheets("Modifikasi").Range("MonthAvgMod"), Relation:=2, FormulaText:=0
SolverAdd CellRef:=Worksheets("Modifikasi").Range("WeekAvgMod"), Relation:=2, FormulaText:=0
SolverSolve UserFinish:=True
Dim MeanMSEMod, StdDevMSEMod As Double
'highlight outliers
MeanMSEMod = Application.WorksheetFunction.Average(Range(MSEMod))
StdDevMSEMod = Application.WorksheetFunction.StDev(Range(MSEMod))

Display More

**royUK** · Dec 21st 2020

Does the range MSEMod exist?

amde10 · Dec 21st 2020

Yeah, it already exists in Worksheets'name Modif, column I, start from row 22 until last data in the same column

**royUK** · Dec 21st 2020

Have you checked if the formulas are being added to the sheet correctly?

Fluff13 · Dec 21st 2020

If they are named ranges try

Code

amde10 Dec 21st 2020