Bulk Goal Seek Macro Breaks when encountering Blank Value

[BM604]

Hello guys,

​

Been a long time lurker of this amazing site. Lots of great resources.

I need to do a goal seek on a large excel file line by line. I found the following Macro module that helps automate the process:

Sub BulkGoalSeek()
   Dim C       As Range
   For Each C In Range("A2:A10")
      C.Offset(0, 1).GoalSeek Goal:=C.Offset(0, 2), ChangingCell:=C
   Next C
End Sub

Please see the image of my doucment here: http://uploadpie.com/SQmvm

Here you can see I have the macro set to run from A2 to A10. You will notice that there are gaps in the data in my file and between rows 6 to 8 the cells are blank. The fact these cells are blank in effect "breaks" the macro. I get the error: Run-time error '1004: Reference is not valid.

How can I get this to continue to run from A2 to A10 by ignoring the blanks?

Any direction is greatly appreciated. Thank you everyone.

Edit: Running isblank(cell) on the "blank cells" that break the macro comes up FALSE. If I make a formula =A6="" that will report as true though. Just wanting to clarify if it makes a difference.

 

[FormR]

Hi, welcome to the forum!

Not tested, but you could try:

Sub BulkGoalSeek()
Dim C       As Range
For Each C In Range("A2:A10")
    If C.Value <> "" Then
        C.Offset(0, 1).GoalSeek Goal:=C.Offset(0, 2), ChangingCell:=C
    End If
Next C
End Sub

 

[BM604]

Wow I think that just worked out on my initial test run! Thanks so much FormR! I hope you have a great weekend my friend.

 

[FormR]

That's great, thanks for letting us know.

I hope you have a great weekend my friend.

Same to you