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

[Pantona]

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

[Ratslinger]

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.

[Pantona]

Thank you very much!