VBA Goal Seek with Moving Reference Cell

[InOverMyH3ad]

Hello All,
I have a simple goal seek code in a sheet. However, we frequently add rows above where this goal seek happens. The BJ column will always be the same, but the row number will change. Is there any way to have the cell reference change in the VBA as the sheet expands/contracts? I've searched online for help but it's hard to phrase exactly what I'm looking for and find valid results. Any assistance would be greatly appreciated!

Private Sub CommandButtonX_Click()
Range("BJ736").GoalSeek _
    Goal:=Range("BJ759"), _
    ChangingCell:=Range("BJ751")

End Sub

 

[jim may]

Could/Can you possible assign Range Names to each GoalSeek element?

Here's a sample of a Macro I use in a GoalSeek environment I use Often.

Sub UpdateGoalSeek()
    Range("End_Bal").GoalSeek Goal:=0, ChangingCell:= _
        Range("CMP")
End Sub

In my case my elements don't Jump around the worksheet or change their cell locations, but still Range Names are by design suppose to assist in taking care of this situation if it exists.

FWIW...

Jim