VBA goalseek range

[MetalBee]

I am new to VBA and using Excel 2010.

I need to apply a goalseek to each row in range T2:T19698, set to value 0.4, by changing each value in range S2:S19698

Can anyone help please?

 

[michaelsmith559]

You can try this:

Sub Goal_Seek_Column()
Application.ScreenUpdating = False
Dim i As Long, lastrow As Long

With ActiveSheet
lastrow = Cells(Rows.Count, "T").End(xlUp).Row

For i = 2 To lastrow
    Range("T" & i).GoalSeek Goal:=0.4, ChangingCell:=Range("S" & i)
Next i

End With
Application.ScreenUpdating = True
End Sub

 

[MetalBee]

Thanks Michael, your solution didn't quite work for me (it froze my system, or perhaps it was calculating all those goal seeks?!) - the screenupdating = false made me unsure. but you gave me an idea:

Sub GoalSeek_Range()
For j = 2 To 19698
Cells(j, "T").GoalSeek Goal:=0.4, ChangingCell:=Cells(j, "S")
Next j
End Sub

 

[MetalBee]

Yes your solution works, and is better than my variant, many thanks to you.

 

[michaelsmith559]

Glad it worked. Yes, that was a lot of cells to process. I was not sure how long it would take.

 

[MetalBee]

It took about 45 minutes on a server drive - good excuse to slack off for the afternoon