在Excel VBA模块中使用目标查找功能

Question

谁能帮助我理解为什么第一个子工作，但第二个给出了无效的引用?

Sub tester1()
    Range("Q5").GoalSeek goal:=2000000, changingcell:=Range("P5")
End Sub

Sub tester3()
    Dim rngRow As Range
    For Each rngRow In Range("P5").Rows
        rngRow.Cells(0, 1).GoalSeek goal:=2000000, changingcell:=rngRow.Cells(0, 0)
    Next rngRow
End Sub

Answer 1

Cells(0, 1)和Cells(0, 0)无效-行和列必须大于0

Range("A1") = Cells(1, 1)    'Cells(row, col)
Range("A2") = Cells(2, 1)
Range("B1") = Cells(1, 2)
Range("B2") = Cells(2, 2)

---

Test with this:

Sub tester3()
    Dim cel As Range
    For Each cel In Range("Q5:Q10")
        cel.GoalSeek Goal:=2000000, ChangingCell:=cel.Offset(0, -1)
    Next rngRow
End Sub