Macro for running goal seek in a column until there is no more data :)

[jlernfelt]

Took a VBA course a couple of years ago but man that knowledge went in and out

I have x number of rows of data. I would like to run a macro that goal seeks by setting each cell in column R to 0 (zero) by changing the cells in the corresponding row in column Q, until there is no more rows with data. there will be no gaps between rows. I've recorded a macro that runs the goal seek every time I click an assigned button or alternatively runs the macro by shortcut but it would be nice to just make the macro run everything at once. I guess it doesn't take a lot of code.

So..who is up for this easy challenge?

thanks in advance, great forum!

 

[JoeMo]

Sub GoalSeekToZero()
Dim Adjust As Range
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
For Each Adjust In Range("Q2:Q" & Cells(Rows.Count, "R").End(xlUp).Row)
    Adjust.Offset(0, 1).GoalSeek goal:=0, changingcell:=Adjust
Next Adjust
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub

 

[poulmax]

Hi

Maybe You can use this code snip, I use it every time I must loop through lots of rows.

Set AktCel_opt = AktSht.Range("R1")
Set NxtCel_opt = AktCel_opt.Offset(1, 0)

Do While Not IsEmpty(AktCel_opt)
Set NxtCel_opt = AktCel_opt.Offset(1, 0)

'************************************************
' Here is the code You wants to run for every row
' Could bee a new loop change the Q- cell until
' R- is zero
'*************************************************


Set AktCel_opt = NxtCel_opt
Loop

 

[jlernfelt]

Hi

Maybe You can use this code snip, I use it every time I must loop through lots of rows.

Set AktCel_opt = AktSht.Range("R1")
Set NxtCel_opt = AktCel_opt.Offset(1, 0)

Do While Not IsEmpty(AktCel_opt)
Set NxtCel_opt = AktCel_opt.Offset(1, 0)

'************************************************
' Here is the code You wants to run for every row
' Could bee a new loop change the Q- cell until
' R- is zero
'*************************************************


Set AktCel_opt = NxtCel_opt
Loop

Thanks, makes sense. I found a code online and modified it. It worked but are there any pitfalls?

Public Sub GoalSeeker()


' Application.ScreenUpdating = False
' Application.Calculation = xlCalculationManual


With Sheets("Sheet1")


For I = 3 To .Cells(.Rows.Count, "R").End(xlUp).Row
.Cells(I, "R").GoalSeek Goal:=0, ChangingCell:=.Cells(I, "Q")
Next I


End With


' Application.ScreenUpdating = True
' Application.Calculation = xlCalculationAutomatic


End Sub

 

[JoeMo]

Did you see post #2 ?

 

[jlernfelt]

Joe, OMG I'm sorry, I missed it. Perfect, thank you very much!

 

[JoeMo]

Joe, OMG I'm sorry, I missed it. Perfect, thank you very much!

You are welcome - thanks for the reply.