Macro to perform Goal Seek for many columns

[Matt_86]

Hi,

I need to perform goalseek to set the cells in row 61 (from column G onwards) to zero by changing the cells in row 40 (column G onward)

I can record a macro to do this for only 3 columns (as in my spreadsheet) but the problem is I will have approx 100 columns worth of different data and I need to perform the same Goal Seek function for each different column.

In other words I want to perform the following action:

Range("G61").goalseek Goal:=0, ChangingCell:=Range("G40")
End Sub

Except I need to do so for the subsequent columns H, I, J, K, L etc etc and I'm not sure how to amend the macro to do this(?)

Any help = greatly appreciated

 

[Matt_86]

Someone please help..anyone.....surely it can't be that complex a problem but it is beyond my ultra-basic VBA capability

 

[VoG]

Something like this?

Sub Gseek()
Dim LC As Long, j As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = 7 To LC
    Cells(61, j).GoalSeek Goal:=0, ChangingCell:=Cells(40, j)
Next j
End Sub

 

[Matt_86]

VoG you champion.
The initial code below didn't work for some reason but I just removed the LC reference and set j = 7 To 100 (to perform 100 columns' worth of Goal Seeks).