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