VBA - Goal Seek where columns change each week

[bamf3000]

So I'm trying to create a quick and easy goal seek macro, but every week the columns will shift over 1, so setting a static cell won't work. Is there a way to offset the goal seek or have it find a certain value and use that column?

This is what I have so far, but I can't seem to get further than this after searching.

Sheets("HL Allocation").Range("BH60").Select

Sheets("HL Allocation").Range("BH60")Offset(1).GoalSeek Goal:=Sheets("MLB").Range("F73").Value, ChangingCell:=Sheets("HL Allocation").Range("BH43").Offset(1)


Thanks!

 

[bamf3000]

Can someone let me know if this is even possible with goal seek?

 

[bamf3000]

Bump for some help