Hi there I am trying to create a function which uses the solver function and then calling this function in a sub which will eventually loop through a number of instances. I have tried to set up the sub for a single instance as a test but I am getting a runtime error 438 method range of object worksheet failed. Please see code below:
Function DepreciationProfilingMethod(WS As Worksheet, Var As Range, Adj As Range)
<code>SolverReset
SolverOptions Assumenonneg:=False
SolverOK SetCell:=WS.Range("Var"), MaxMinVal:=3, ValueOf:=0, ByChange:=WS.Range("Adj"), EngineDesc:="GRG Nonlinear"
SolverSolve (True)
End Function
Sub RunDepreciationMacro()
Dim C_TRS As Excel.Worksheet Dim WorkB As Workbook
Set C_TRS = ThisWorkbook.Sheets("C_TRS")
Set WorkB = ThisWorkbook
Call addsolver 'Error in this lineCall DepreciationProfilingMethod(C_TRS, C_TRS.Range("Var_2023"), C_TRS.Range("Adj_2023"))
End Sub
</code>
<code>SolverReset
SolverOptions Assumenonneg:=False
SolverOK SetCell:=WS.Range("Var"), MaxMinVal:=3, ValueOf:=0, ByChange:=WS.Range("Adj"), EngineDesc:="GRG Nonlinear"
SolverSolve (True)
End Function
Sub RunDepreciationMacro()
Dim C_TRS As Excel.Worksheet Dim WorkB As Workbook
Set C_TRS = ThisWorkbook.Sheets("C_TRS")
Set WorkB = ThisWorkbook
Call addsolver 'Error in this lineCall DepreciationProfilingMethod(C_TRS, C_TRS.Range("Var_2023"), C_TRS.Range("Adj_2023"))
End Sub
</code>
SolverReset
SolverOptions Assumenonneg:=False
SolverOK SetCell:=WS.Range("Var"), MaxMinVal:=3, ValueOf:=0, ByChange:=WS.Range("Adj"), EngineDesc:="GRG Nonlinear"
SolverSolve (True)
End Function
Sub RunDepreciationMacro()
Dim C_TRS As Excel.Worksheet Dim WorkB As Workbook
Set C_TRS = ThisWorkbook.Sheets("C_TRS")
Set WorkB = ThisWorkbook
Call addsolver 'Error in this lineCall DepreciationProfilingMethod(C_TRS, C_TRS.Range("Var_2023"), C_TRS.Range("Adj_2023"))
End Sub
Thank you very much in advance!
I have tried to add a worksheet as a parameter to the function but this has not resolved the error and not sure how to resolve this.