Goal Seek with dynamic Goal Seek
 

The below is to handle a Goal Seek in which the "Set Cell" and "To Value" can
vary. No matter what I do to this, I keep getting shot down with Reference
not valid..

Recording a macro gives me:
Sheets("Calcs").Range("L22").GoalSeek Goal:=558226, ChangingCell:=Range( _
"B42")

What changes in the above is to determine the cell on the "Calcs" that has
the target value and to what value.


Sub GoalSeekCSVR1C1()
'
' GoalSeekCSV Macro
'
Set wb = ThisWorkbook
Set wsGUI = wb.Worksheets("GUI")
Set wsCalcs = wb.Worksheets("Calcs")
intTargetCellRow = wsGUI.Range("TargetCellRow")
intTargetCellCol = wsGUI.Range("TargetCellCol")
wsGUI.Activate

varGoal = wsGUI.Range("TargetAmount").Value
strChangeRange = wsGUI.Range("B42").Address
wsCalcs.Cells(intTargetCellRow, intTargetCellCol).GoalSeek
Goal:=varGoal, ChangingCell:=strChangeRange

Set wsGUI = Nothing
Set wsCalcs = Nothing
Set wb = Nothing
End Sub

Goal Seek with dynamic Goal Seek
 

I solved my own problem.

"Dkline" wrote:

The below is to handle a Goal Seek in which the "Set Cell" and "To Value" can
vary. No matter what I do to this, I keep getting shot down with Reference
not valid..

Recording a macro gives me:
Sheets("Calcs").Range("L22").GoalSeek Goal:=558226, ChangingCell:=Range( _
"B42")

What changes in the above is to determine the cell on the "Calcs" that has
the target value and to what value.


Sub GoalSeekCSVR1C1()
'
' GoalSeekCSV Macro
'
Set wb = ThisWorkbook
Set wsGUI = wb.Worksheets("GUI")
Set wsCalcs = wb.Worksheets("Calcs")
intTargetCellRow = wsGUI.Range("TargetCellRow")
intTargetCellCol = wsGUI.Range("TargetCellCol")
wsGUI.Activate

varGoal = wsGUI.Range("TargetAmount").Value
strChangeRange = wsGUI.Range("B42").Address
wsCalcs.Cells(intTargetCellRow, intTargetCellCol).GoalSeek
Goal:=varGoal, ChangingCell:=strChangeRange

Set wsGUI = Nothing
Set wsCalcs = Nothing
Set wb = Nothing
End Sub