I am just a novice & wrote a simple do loop for goal seeking. The goal seek
calculates however, the macro won't End Sub. The debugging highlights the
3rd to the last line below : Cells(81, i).GoalSeek Goal:=0,
ChangingCell:=Cells(74, i)



Sub GoalSeek()

'select/copy values
Range("AY79:BJ79").Copy
Range("AY76:BJ76").Select
Selection.PasteSpecial Paste:=xlValues


'set variables
Dim r As Integer, i As Integer
'assign column value to varibles
r = 50
i = 0

Cells(81, r).Select

Do
r = r + 1

Loop Until Cells(81, r) = "end"

For i = 51 To r

'goal seek
'set cell range (row 81) to 0 by adjusting 100% (row 74)
Cells(81, i).GoalSeek Goal:=0, ChangingCell:=Cells(74, i)

Next i

End Sub

There is nothing in your macro that would make Cells(81, r) = "end".

That is why your loop runs continuously. You could set it to loop until r =
456 which would be the last column of the sheet up throught XL2003. It goes
a lot higher than that in 2007.

"Sharon" wrote:

> I am just a novice & wrote a simple do loop for goal seeking. The goal seek
> calculates however, the macro won't End Sub. The debugging highlights the
> 3rd to the last line below : Cells(81, i).GoalSeek Goal:=0,
> ChangingCell:=Cells(74, i)
>
>
>
> Sub GoalSeek()
>
> 'select/copy values
> Range("AY79:BJ79").Copy
> Range("AY76:BJ76").Select
> Selection.PasteSpecial Paste:=xlValues
>
>
> 'set variables
> Dim r As Integer, i As Integer
> 'assign column value to varibles
> r = 50
> i = 0
>
> Cells(81, r).Select
>
> Do
> r = r + 1
>
> Loop Until Cells(81, r) = "end"
>
> For i = 51 To r
>
> 'goal seek
> 'set cell range (row 81) to 0 by adjusting 100% (row 74)
> Cells(81, i).GoalSeek Goal:=0, ChangingCell:=Cells(74, i)
>
> Next i
>
> End Sub
>

I think JLGWhiz meant 256 not 456!

the rest he sais is true.

cheers

carlo

On Nov 15, 1:18 pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
> There is nothing in your macro that would make Cells(81, r) = "end".
>
> That is why your loop runs continuously. You could set it to loop until r =
> 456 which would be the last column of the sheet up throught XL2003. It goes
> a lot higher than that in 2007.
>
>
>
> "Sharon" wrote:
> > I am just a novice & wrote a simple do loop for goal seeking. The goal seek
> > calculates however, the macro won't End Sub. The debugging highlights the
> > 3rd to the last line below : Cells(81, i).GoalSeek Goal:=0,
> > ChangingCell:=Cells(74, i)
>
> > Sub GoalSeek()
>
> > 'select/copy values
> > Range("AY79:BJ79").Copy
> > Range("AY76:BJ76").Select
> > Selection.PasteSpecial Paste:=xlValues
>
> > 'set variables
> > Dim r As Integer, i As Integer
> > 'assign column value to varibles
> > r = 50
> > i = 0
>
> > Cells(81, r).Select
>
> > Do
> > r = r + 1
>
> > Loop Until Cells(81, r) = "end"
>
> > For i = 51 To r
>
> > 'goal seek
> > 'set cell range (row 81) to 0 by adjusting 100% (row 74)
> > Cells(81, i).GoalSeek Goal:=0, ChangingCell:=Cells(74, i)
>
> > Next i
>
> > End Sub- Hide quoted text -
>
> - Show quoted text -