Using Goal Seek macro over a range

[londonkiwi]

New to this - please don't give me too much of a hard time!
Using Excel 97

I have a column of values (calculated values) G5:G3000 which range from 0 to 5. Their values are altered by changing cell A1 (which holds a formula) and various other caluclations.

Using MS Goal Seek function I want to find out when any value in range G5:G3000 reaches 2 (the users would be requested for this value), by changing cell A1's value. This data would be pasted beside col g5:g3000 eg h5:h3000. as the macro routinme moved down the column. Could I have a button the users could push to initiate this process??

Any code/comments appreciated

 

[danomaniac]

Sure,
Make sure you have your Control Toolbox visible (View>Toolbars>Control Toolbox). Select the Command Button button, and then draw a command button wherever you want on your worksheet. Then right-click on it and select View Code. This opens the code window and paste your code in there.

Hope this helps.

PS. Make sure your sheet is not protected or the control toolbox will be greyed out.

 

[londonkiwi]

This is what I came up with - it works for my situation


Sub GS_1()
'
' GS_1 Macro
' Macro recorded 26/2/2002 '
' Keyboard Shortcut: Ctrl+j
'
' Set cell X6 = 0

Dim bob As String
Dim counter As Integer

Range("$X$6&quot.Select ' Select the cell in which we are pointing the Goal Seek Function to "Seek"
ActiveCell.FormulaR1C1 = "0" ' Set that value to 0 at the start of each "Seek"

Range("AW16&quot.Select ' Select the start point of the column we are calculating on

counter = 16 ' starting at row 16 of column AW

'MsgBox bob

Do Until ActiveCell = "" ' stop when no more values in colum AW

ActiveCell.GoalSeek _
goal:=Range("AW9&quot, _
ChangingCell:=Range("X6&quot

Selection.Copy
Range("x6&quot.Copy
Range("AY" & counter).PasteSpecial ' paste the value in X6 to row AY

Selection.Offset(1, -2).Select ' move down one row and back two columns
counter = counter + 1
Loop

Range("$X$6&quot.Select
ActiveCell.FormulaR1C1 = "0" ' finish and set the value to 0

End Sub