excelvbaloopsseek

Excel VBA GoalSeek by each row in a table

I am looking to perform a goal seek within each row of a specified table. The following VBA code successfully executes the GoalSeek.

Sub GoalSeek()
 Dim lo As ListObject
 Dim rw
 Set lo = Sheet1.ListObjects("Table1")

 For rw = lo.DataBodyRange.Rows.Count To 1 Step -1
  Range("M" & rw).GoalSeek Goal:=Range("N" & rw).Value, ChangingCell:=Range("E" & rw)
 Next
End Sub

It fails to execute all the way through and generates run-time error 1004: GoalSeek method of Range class failed

enter image description here f

Any suggestions on how to resolve? I have attached a copy of the workbook below.

Excel file copy

Solution

  • In your loop you have reach the first row and it is not possible to GoalSeek that row. so change this line of code

    For rw = lo.DataBodyRange.Rows.Count To 1 Step -1  
to =>  
For rw = lo.DataBodyRange.Rows.Count To 2 Step -1