Ask Your Question
0

LibreOffice Calc: “Goal Seek” macro with target cell instead of target value?

asked 2019-07-09 18:58:13 +0200

Pantona gravatar image

Assumed we have this simple macro code for goal seek a value:

oGoal = seekGoal(oFormulaCell.CellAddress, oVariableCell.CellAddress, "20")

Would it be possible to replace the current value "20" with the value taken from a cell, e.g. taken from the cell oTargetValueCell.CellAddress?

I would love to use something like:

oGoal = seekGoal(oFormulaCell.CellAddress, oVariableCell.CellAddress, oTargetValueCell.CellAddress)

If it may help, here you can see the whole current (working) macro code:

Sub Goalseek

    Dim Sheet2
    Dim oFormulaCell
    Dim oTargetValueCell
    Dim oVariableCell
    Dim oGoal

oSheet = ThisComponent.Sheets(0)

oFormulaCell = oSheet.getCellRangeByName("A1")
oTargetValueCell = oSheet.getCellRangeByName("A2")
oVariableCell = oSheet.getCellRangeByName("A3")

oGoal = ThisComponent.seekGoal(oFormulaCell.CellAddress, oVariableCell.CellAddress, "20")
oTargetCell.setValue(oGoal.Result)

End Sub
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-07-09 19:59:28 +0200

Ratslinger gravatar image

Hello,

It is simply a matter of retrieving the Value of the Cell. Tested with following routine:

Sub Goalseek
    Dim Sheet2
    Dim oFormulaCell
    Dim oTargetValueCell
    Dim oVariableCell
    Dim oGoal
    oSheet = ThisComponent.Sheets(0)
    oFormulaCell = oSheet.getCellRangeByName("A6")
    oTargetValueCell = oSheet.getCellRangeByName("D3")
    oVariableCell = oSheet.getCellRangeByName("A3")
    oGoal = ThisComponent.seekGoal(oFormulaCell.CellAddress, oVariableCell.CellAddress, oTargetValueCell.getValue)
    oVariableCell.setValue(oGoal.Result)
End Sub

Here is the Sample used ---- GoalSeek.ods

Modify the value in D3 and press button. Result in A3.

edit flag offensive delete link more

Comments

Thank you very much! :-)

Pantona gravatar imagePantona ( 2019-07-09 20:51:30 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-07-09 18:58:13 +0200

Seen: 21 times

Last updated: Jul 09