Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Goal Seek | Excel Worksheet Functions | |||
Goal Seek | Excel Discussion (Misc queries) | |||
vba goal seek 2 | Excel Programming | |||
Goal Seek | Excel Discussion (Misc queries) | |||
Goal Seek | Excel Worksheet Functions |