VBA Goalseek Offset

Hi all,

Bit of a tricky one, or possibly not.
I am trying to code through a spreadsheet to allow me to update a column of values based on a two variable condition.
The basic idea is that Column J contains values (angles) related to the difference in z elevations between cells in column C.

Where the value of the angle in column J is greater than 15 degrees I need to check column C and determine whether the offset value on the same row is greater than or less than the offset value in the row below it or less than the row below it.
In relation to Column J, Where the value in column C offset (0,-7) is greater than the value in column C offset (1,-7) I need to raise the value in (0-7) so that the formula calculating J updates to a value of equal to 15 degrees.
Where the value in Column C offset (0-7) is less than the value in column C offset (1,-7), I need to raise the value in (1,-7) so that the formula calculating J updates to a value of equal to 15 degrees.

The key point is that the value cannot be reduced in Column C, only increased in order to reduce the angle in Column J.

Below is some of the code I tried, with no luck:

Private Sub ChangeCell()
Dim Target As range
Dim B As Integer
Set Target = range("J4:J100")
B = 15


For Each cell In Target

If cell.Value > B Then
cell.Offset(0, -7).Select
Selection.Activate

With Selection
If cell.Value < cell.Offset(1, 0).Value Then
cell.Offset(0, 7).GoalSeek goal:=15, changingcell:=ActiveCell.Value
ElseIf cell.Value > cell.Offset(1, 0).Value Then
cell.Offset(1, 7).GoalSeek goal:=15, changingcell:=cell.Offset(1, 0).Value
End If
End With
End If
Next

End Sub

The attachment button doesn't seem to be working for me, but please see attached screenshot of the columns / spreadsheet.
If anyone can help it would be very much appreciated.

Cheers,

David
Untitled.png

Hi,

Many thanks for the tip.
Do you have a suggestion as to how I might get this to work?
The range will have up to 40,000 values so referencing them individually is going to be impossible. Furthermore the Goalseek function does not exist as a standalone formulaic function for input directly into the spreadsheet.

I figured it out.
Thank again for the great tip, it dawned on me what to do a few hours later after some time out on Gran Turismo.
Solution below:


Private Sub ChangeCell()
Dim Target As Range
Dim B As Integer
Dim y As Range
Dim x As Range
Dim w As Range
Dim z As Range
Set Target = Range("J4:J100")
B = 15


For Each cell In Target

If cell.Value > B Then

Set x = cell.Offset(0, -7)
Set y = cell.Offset(0, 0)
Set w = cell.Offset(1, -7)

If x < w Then
y.GoalSeek goal:=15, changingcell:=x
ElseIf x > w Then
Set z = cell.Offset(0, 1)
z.GoalSeek goal:=15, changingcell:=w
End If
End If
Next

End Sub