0

I'm trying to goalseek in excel, where the goalseeking value is in a dynamic cell that is dependent on other inputs (here the "MaxAcquisitionYear" and the "YearRange" inputs)

I get the correct location of the goalseek cell, but the goalseek function is resulting in the error "Object variable or With block variable not set"

Do you know what I'm doing wrong?

Sub MacroTest1()

ActiveSheet.Calculate

Dim LeverageRatio As Range

r = Range("LeverageRatioPeriod").Row
c = WorksheetFunction.Match(Range("MaxAcquisitionYear"), Range("YearRange"), 0)
LeverageRatio = Cells(r, c)


Range("MaxEV").Select

Range(LeverageRatio).GoalSeek Goal:=4, ChangingCell:=Range("MaxEV")

End Sub

Basically, I want it to paste a value in the cell "MaxEV" that causes the value in "LeverageRatio" to equal 4.

Pernille
  • 23
  • 3
  • Since `LeverageRatio` is a `Range`, it needs `Set`: `Set LeverageRatio = Cells(r, c)`. Then change `Range(LeverageRatio)` to just `LeverageRatio`. – BigBen Feb 16 '22 at 14:25

1 Answers1

0

Delete the line LeverageRatio = Cells(r, c). This is causing the error.

Change your last line to:

Cells(r, c).GoalSeek Goal:=4, ChangingCell:=Range("MaxEV")

Ozgun Senyuva
  • 466
  • 5
  • 12