Goal Seek with Excel Macro

[john1212]

I am using a record of an Excel Macro for a Goal Seek function on Excel. The problem is that when I try to add a new row in my sheet (both above the cells running the Goal Seek or even in the middle of them), the Macro that i recorded cannot be used anymore and I need to record it again (the error that i receive if i try to run it is:"1004, reference is not valid"). How could I fix this so that I don't need to record everytime the same Macro when I add/delete a row? Please help me. I attach below the pics and the VBA code

Sub Macro17()
'
' Macro17 Macro
'

'
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.GoalSeek Goal:=0, ChangingCell:=ActiveCell.Offset(-9, 0).Range( _
        "A1")
End Sub

 

[gravanoc]

I am using a record of an Excel Macro for a Goal Seek function on Excel. The problem is that when I try to add a new row in my sheet (both above the cells running the Goal Seek or even in the middle of them), the Macro that i recorded cannot be used anymore and I need to record it again (the error that i receive if i try to run it is:"1004, reference is not valid"). How could I fix this so that I don't need to record everytime the same Macro when I add/delete a row? Please help me. I attach below the pics and the VBA code

Sub Macro17()
'
' Macro17 Macro
'

'
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.GoalSeek Goal:=0, ChangingCell:=ActiveCell.Offset(-9, 0).Range( _
        "A1")
End Sub

Hi. Best course of action in my opinion would be to save the Goal Seek cells as a Named Range. There are several ways to do this, but the fastest would be to type a name into the Name Box to the left of the formula bar with the changing cell selected. Also do the same with a different name for the result cell.

Next you can make modifications to the code as follows:

Remove both Application.CutCopyMode = False (not needed). I don't know what the name of your sheet is, so replace the name I put in the next line with it's name. Also change the names I use inside the Ranges to whatever you used for the result & changing cells.

Sub Macro 17()
Worksheets("Sheet X").Range("GoalSeekResultCell").GoalSeek Goal:=0, ChangingCell:=Worksheets("Sheet X").Range("ChangeCell")
End Sub

That should work, but I haven't tested it since I'm on mobile.