VBA GoalSeek Loop Issue

Question

I am a novice regarding VBA and in my first code I try to make a loop through 2 columns (P and J) for a GoalSeek function.

The set cells are P2 then P3 then P4 etc. While the change cells are J2 then J3 etc.

I want to iterate from row 2 till row N (the number N is stored in the cell D1)

Here's what I wrote:

N = ThisWorkbook.Sheets("Reverse DCF").Range("D1").Value

Dim i As Integer

For i = 2 To N
     Range(Cells((i), "P")).GoalSeek Goal:=0, ChangingCell:=Range(Cells((i), "J"))
Next i

I get an error message:

 "Method 'Range' of object '_Global' failed.

I am sorry but I don't see the problem with my code.

Answer 1

You can use Cells without Range. Here is an working example. Make sure cell Ncontains an integer and Pi to PN contains formulas.

Sub GoalSeekTest()

Dim N As Integer
Dim i As Integer

N = ThisWorkbook.Sheets("Reverse DCF").Range("D1").Value

    For i = 2 To N
        If Cells(i, "P").HasFormula Then
            'Messagebox for clarification
            MsgBox ("Formula cell: " & Cells(i, "P") _
                .Address(RowAbsolute:=False, ColumnAbsolute:=False))

            Cells((i), "P").GoalSeek Goal:=0, ChangingCell:=Cells((i), "J")
        Else
            MsgBox "Cell " & Cells(i, 1) _
                .Address(RowAbsolute:=False, ColumnAbsolute:=False) _
                & " has no formula - skipping this cell!", _
                vbCritical, "Formula required!"
        End If
    Next i
End Sub