Using Goal Seek on multiple columns and rows in VBA

[Asto_82]

Hello,

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
    
        Loop
        
    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.