0

I am trying to create a function which uses the Solver 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)

    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 line
    Call DepreciationProfilingMethod(C_TRS, C_TRS.Range("Var_2023"), C_TRS.Range("Adj_2023"))
End Sub

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.

Improve this question
4
  • Try SolverOK SetCell:=Var, MaxMinVal:=3, ValueOf:=0, ByChange:=Adj, EngineDesc:="GRG Nonlinear". Adj and Var are Range object argument. You can't use it as WS.Range("Var"). Commented Jun 3, 2024 at 0:41
  • Noted thanks, I've updated to remove worksheet, however now I am getting a run=time error 13 type mismatch. Please note that Var is a single cell named range and Adj is a range of cells Commented Jun 3, 2024 at 7:22
  • take the quotes out of "Var" assuming that's the address you're passing. If it's a named range LITERALLY being "VAR" than there's no need for the parameter. Also, be more specific where the error is. A``type` error is just the wrong type of object (so like instead of a number it's getting a range) and should be easy to fix. Commented Jun 3, 2024 at 7:53
  • Thanks very much for your help, I have removed the quotation marks. I am now getting this error: Run-time error 1004, method Range of object _Worksheet failed in the following line of code: Call DepreciationProfilingMethod(C_TRS.Range(Var_2023), C_TRS.Range(Adj_2023)) It feels like a really easy fix, I'm just missing something obvious! Commented Jun 3, 2024 at 8:21
Related questions
651
How can I avoid using Select in Excel VBA?
1
Excel VBA Copy Destination syntax with variables for Workbook/Worksheet
606
Is there a way to crack the password on an Excel VBA Project?
Load 3 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.