Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating GoalSeek - why doesn't this code work?
I have a spreadsheet with calculations set up in columns.
I have used goal seek manually to get the answer I want without incident. However, I want to find a way to do this for each column as in one spreadsheet I have 67 columns to check - a pain to do by hand. I have come up with the following code that VBA/Excel does not like. I would appreciate feedback, corrected code that works and a explanation why it works! Thanks, Jeff --- Sub GoalSeekReinforcement() 'Target cells start in column B 'Blank target cell indicates end of columns to be solved Dim n As Integer Dim tRange As Range Dim sRange As Range Dim tempRange1 As Range Dim tempRange2 As Range Dim Success As Boolean 'Target cell range - all are in row 166 Set tRange = ActiveSheet.Range("B166", Range("B166").End (xlToRight)) 'First change cell - all are in row 26' Set sRange = ActiveSheet.Range("B26") 'Iterate over all columns that may need goal seek For n = 1 To tRange.Columns.Count 'Put initial value in change cell as goal seek requires a non-blank value to modify sRange.Offset(0, n - 1).Cells(1, 1).Formula = "=8" 'Success is dummy boolean Success = tRange.Offset(0, n - 1).Resize(1, 1).GoalSeek (1, sRange.Offset(0, n - 1).Resize(1, 1)) Next n End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Goalseek gives incorrect results | New Users to Excel | |||
Problem with Goalseek Macro | Excel Discussion (Misc queries) | |||
GoalSeek in VBA | Excel Discussion (Misc queries) | |||
Automating data copying between work sheets | New Users to Excel | |||
XL 2000: automating drag-and-drop for worksheet ranges. Using Code! | Excel Programming |