Matt_Clarke
07-14-2009, 05:48 AM
Hi Everyone, my first post here
1. Version of the program
2. What you want it to do
3. Cell references, bookmark names, column letters, row numbers, worksheets, styles, whatever pertains to the information at hand
4. Error messages if any
5. If not the entire code, then at least some of it
6. Sample data (before and after sample worksheets, add as attachments here)
7. Politeness and gratitude
8. Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
1) Excel 2003
2)/3) Perform multiple goalseeks, using columns as Ranges for "Set value" (B2:B4), "Desired value" (A2:A4) and "Changing Cell" (C2:C4)
4) Runtime Error '1004': Reference not valid.
5) brettdj's Multiple GoalSeek Macro (very kindly made available, thank you).
The macro gets stuck in the Goal Seek For...Next loop:
' Loop through the goalseek method
For i = 1 To TargetVal.Columns.Count
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).Value, ChangingCell:=ChangeVal.Cells(i)
Next i
6) See attached sheet
Thank you very much anyone who can tell me what is going wrong here, why, and how to avoid this problem from continuing.
Matt
1. Version of the program
2. What you want it to do
3. Cell references, bookmark names, column letters, row numbers, worksheets, styles, whatever pertains to the information at hand
4. Error messages if any
5. If not the entire code, then at least some of it
6. Sample data (before and after sample worksheets, add as attachments here)
7. Politeness and gratitude
8. Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
1) Excel 2003
2)/3) Perform multiple goalseeks, using columns as Ranges for "Set value" (B2:B4), "Desired value" (A2:A4) and "Changing Cell" (C2:C4)
4) Runtime Error '1004': Reference not valid.
5) brettdj's Multiple GoalSeek Macro (very kindly made available, thank you).
The macro gets stuck in the Goal Seek For...Next loop:
' Loop through the goalseek method
For i = 1 To TargetVal.Columns.Count
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).Value, ChangingCell:=ChangeVal.Cells(i)
Next i
6) See attached sheet
Thank you very much anyone who can tell me what is going wrong here, why, and how to avoid this problem from continuing.
Matt