stepping through rows using offset function

[staderennais]

Hi,
I've done some programming but I'm completely new at VBA. I searched the archives but couldn't find a direct example or answer to my question. I apologize if this is a dumb question.

I'm trying to solve a nonlinear equation using goal seek. The output from that equation is then being used in an optimization routine. The point is that I need goalseek to update the row every time before the solver runs another iteration. However, I keep getting this error:

"Run time error '1004' "

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim M As Integer

For M = 0 To 99

Worksheets("Sheet1").Range("Offset(j7, M, 0, 1, 1)").GoalSeek _
Goal:=0, _
ChangingCell:=Worksheets("Sheet1").Range("Offset(i7, M, 0, 1, 1)")

Next M

End Sub




Thanks for any help you can offer.

 

[brian.wethington]

See if this does what you need

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim M As Integer 

For M = 0 To 99 

Worksheets("Sheet1").Range("J7").Offset(M, 0).GoalSeek _ 
Goal:=0, _ 
ChangingCell:=Worksheets("Sheet1").Range("I7").Offset(M, 0) 

Next M 

End Sub

Offset is used a bit different with code.

 

[staderennais]

thanks

Perfect, that did exactly what I wanted, thanks for the help.