Using Goal Seek on multiple columns and rows in VBA


New Member
Jan 30, 2019

I have been using a macro for a while now to perform a Goal Seek on several cells in a row one after the other:

Sub PTUSeek()
Dim Cell, OS, Target, Adj

Set Target = [I4:FW4]
Set Adj = [I3]

OS = Adj.Row - Target.Cells(1).Row
    For Each Cell In Target.Cells
        Cell.GoalSeek 1.5, Cell.Offset(OS)
    Next Cell
End Sub

This changes the cell in row 3 to get the target of 1.5 in row 4, then moves onto the next cell in row 3. I am not very experienced at VBA and admittedly got this from an internet search a couple of years ago.

I have to apply this to several rows, so I want to make this better by jumping to the next row that needs changing (row 5) to get the target of 1.5 in the next result row (row 6), i.e. it needs to jump two rows and start again and keep doing this until there is no more data.

I found a post that found a way to do this but the other way round (i.e. the rows and columns reversed) and have tried to invert it so it works, but no luck:

Sub OrdersClever()

Dim c As Long, r As Long
    'from row 4 to the last used row step every 2nd row
    For r = 4 To Cells(Rows.Count, 6).End(xlUp).Row Step 2
        c = 6    'Start column
        Do Until Cells(r, c).Value = Cells(r, 16).Value
            If Cells(r, c).Value <> "" Then
                Cells(r - 1, c).Value = Cells(r - 1, c).Value
                Cells(r, c).GoalSeek Goal:=1.5, ChangingCell:=Cells(r - 1, c)
            End If
            c = c + 1
    Next r
End Sub

I get a 'Run-time error '1004': Reference isn't valid' for the line Cells(r, c).GoalSeek Goal:=1.5, ChangingCell:=Cells(r - 1, c)

Any help would be much appreciated.

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics