Macro to perform Goal Seek for many columns

Matt_86

New Member
Joined
Apr 7, 2011
Messages
3
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
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Matt_86

New Member
Joined
Apr 7, 2011
Messages
3
Someone please help..anyone.....surely it can't be that complex a problem but it is beyond my ultra-basic VBA capability
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Something like this?

Code:
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

New Member
Joined
Apr 7, 2011
Messages
3
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,987
Members
409,614
Latest member
wile2u

This Week's Hot Topics

Top