VBA Code for bulk Goal Seek

[leeandoona]

I'm trying (and failing) with some VBA code to make a 'Bulk Goal Seek'. My Goal Seek is as follows:

Set Cell Range is G2:G1001
Value I want is 12.00%
Change Cell Range is B2:B1001

So I want to Goal Seek Cell G and convert it to 12% (or closest match) by changing cell B. The other cells contain all the workings that result in a value of 12% but I want to go down 1000 rows repeating this so I hoped I could borrow some code to make it run all at once. Here is the code I was playing with:

Sub BulkGoalSeek()
Dim C As Range
For Each C In Range("B2:B1001")
C.Offset(0, 1).GoalSeek Goal:=C.Offset(0, 2), ChangingCell:=C
Next C
End Sub

Its not workign though. As I borrowed it from another forum, I may be missing something obvious because it does do something, but it runs and changes G to the wrong end result, giving a minus number. Any ideas?

 

[shg]

Sub lee()
  Dim iRow As Long
  
  For iRow = 2 To 1001
    Cells(iRow, "G").GoalSeek Goal:=0.12, ChangingCell:=Cells(iRow, "B")
  Next iRow
End Sub

 

[leeandoona]

Worked brilliantly! Quite speedy too considering how many rows this is over. I see what I did wrong now too so that;s a great big thank you for that. Top job!

 

[shg]

You're welcome.