Offset and goalseek

[RussHatt]

hi, I am struggling to get an offset to work on a goal seek i am trying to do. Essentially i want to goal seek a value in row1, column1 to zero by changing the value of the cell in row 3, column 1. In the next round of calculations, ie when i apply the offset, I want to goalseek row1, column2 to zero by changing the cell in row 3, column 2. Sounds simple but i am doing something wrong. the first calculation works but as soon as i calculate the next column then it breaks down... any ideas...

Sub MySteppedGoalSeek()

Dim ValueAtPt1 As Range
Dim ValueAtPt2 As Range
Dim counter As Integer


counter = 1
Do
Set ValueAtPt1 = Range("D27").Offset(0, counter)
Set ValueAtPt2 = Range("D31").Offset(0, counter)
ValueAtPt1.GoalSeek goal:=0, changingcell:=ValueAtPt2
counter = counter + 1
Loop Until counter = 10


End Sub

 

[StephenCrump]

Welcome to the Forum.

As written, your code will try to vary E31 to get E27 = 0, F27 to get F31 to 0, ... M31 to get M27 to 0.

I'm not sure how this fits with your description of Row 1 and Row 3, nor what you mean by "breaks down"?

 

[RussHatt]

hi, I am struggling to get an offset to work on a goal seek i am trying to do. Essentially i want to goal seek a value in row1, column1 to zero by changing the value of the cell in row 3, column 1. In the next round of calculations, ie when i apply the offset, I want to goalseek row1, column2 to zero by changing the cell in row 3, column 2. Sounds simple but i am doing something wrong. the first calculation works but as soon as i calculate the next column then it breaks down... any ideas...

Sub MySteppedGoalSeek()

Dim ValueAtPt1 As Range
Dim ValueAtPt2 As Range
Dim counter As Integer


counter = 0
Do
Set ValueAtPt1 = Range("D27").Offset(0, counter)
Set ValueAtPt2 = Range("D31").Offset(0, counter)
ValueAtPt1.GoalSeek goal:=0, changingcell:=ValueAtPt2
counter = counter + 1
Loop Until counter = 10


End Sub

Thanks for answering Stephen. I found the issue! Your description of what it should do, is what i wanted it to do. The rows and cols was supposed to make it easier to understand. Fail on my part. The real issue turns out that i had a formula in the ValueAtPt2 row. Excel didnt warn me of my stupidity but rather just returns a reference error 1004. I changed the formula to a number and it now work. Thanks for the help!

 

[StephenCrump]

Great, I'm glad you figured it out.

If you use the Forum again, the more explicit you can be about your problem, the better.

It breaks down doesn't convey a lot of information.

But Run-time error 1004: Reference isn't valid would immediately suggest there is a formula, rather than a value, in the ChangingCell.