Iteration through range().GoalSeek method

[cciitman]

I'm trying to run a goalseek method through several rows of data.

The problem I'm having is that Goalseek Method does not allow ranges, nor is it allowing the cell references to be variables that I can iterate through.

I've tried simple loops with cell names as a variable, naming the range of cells, using .cell(1,1)....I'm stumped.

Anyone have any ideas on tricking the method or am I going about this all wrong?

 

[cciitman]

Forgot to add the code I've tried

Sub SandAdjust()
Dim YieldRef As Range
Dim SandRef As Range

Dim rowIndex As Integer

rowIndex = 18

YieldRef = Cells(rowIndex, 38)
SandRef = Cells(rowIndex, 9)

Range(YieldRef).GoalSeek Goal:=27, ChangingCell:=Range(SandRef)
End Sub

 

[RobBroekhuis]

Something like this should work:

for each cell in range("A1:A20&quot
cell.goalseek 0, cell.offset(0,1)
next cell

This would set the formulas in range A1:A20 to 0 by varying the variables in range ("B1:B20&quot. Is that what you're looking for?
Rob

 

[RobBroekhuis]

I posted at the same time you did. To modify your code, change the line with the goalseek statement to:

YieldRef.GoalSeek Goal:=27, ChangingCell:=SandRef

Rob

 

[cciitman]

Worked great! Thanks a ton.