Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Goal Seek won't End Sub
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Goal Seek won't 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Do Loop Goal Seek won't End Sub
I think JLGWhiz meant 256 not 456!
the rest he sais is true. cheers carlo On Nov 15, 1:18 pm, JLGWhiz 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Goal Seek in a loop | Excel Discussion (Misc queries) | |||
goal seek in a loop | Excel Discussion (Misc queries) | |||
Vba goal seek | Excel Programming | |||
vba goal seek 2 | Excel Programming | |||
Goal Seek | Excel Worksheet Functions |