Using Loop in Goal Seek

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
I am trying to use VBA to Loop a Goal Seek, but for some reason it is not working. Below is my code.

Do
If Not Selection.Value > "" Then Exit Do
Range("aa2").GoalSeek Goal:=44500, ChangingCell:=Range("z2")
Loop

End Sub

In the sample below, column T is the original figure and column Z is the Goal Seek Result. When I run the "Range("aa2").GoalSeek Goal:=44500, ChangingCell:=Range("z2")" portion of the above code it works, but I cannot get it to loop to the bottom of my data like I want. So basically I am having trouble with the code looping to the bottom of my data. Any ideas?
Sylvester - Trans Cost Analysis.xls
TUVWXYZAA
1NumberofChepPalletsAvg.CHEPWeightTotalPalletWeightTotalPalletWeightPlusProductWeightExcessWeightAveWeight/PalletNumberofChepPalletsTotalWeight
2256516257239327,8932,8311544,500
329651885497135,2131,6492949,713
429651885483973,8971,6042948,397
529651885482473,7471,5992948,247
627651755481353,6351,7182748,135
727651755480463,5461,7142748,046
827651755479493,4491,7112747,949
926651690479153,4151,7782647,915
1025651625478943,3941,8512547,894
1127651755478893,3891,7092747,889
1227651755478563,3561,7072747,856
Sylvester
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, trackman69,

so you want to loop from aa2 and z2 to aa200 and z200 (considering that 200 is your last row)

how to find the last cell of your data:
Range("AA65536").End(xlUp).Select

how to loop through cells ?
For i = 2 To 5
MsgBox Cells(i, 27)
Next

result:

Code:
Dim lr as Long
Dim i as Long
lr = Range("AA65536").End(xlUp).Row
For i = 1 To lr
If Cells(i, 27) <> "" Then Cells(i, 27).GoalSeek Goal:=44500, ChangingCell:=Cells(i, 26)
Next i
It's not clear for me what you mean with If Not Selection.Value > "" , so I replaced it with something similar ...

does this give you a start
(I hope so: time to sleep for me)
kind regards,
Erik
 

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
When I try this out, I am told that the reference is not valid and then it highlights in yellow the underlined portion of the code below. I don't understand why it is not working cause it looks fine.


Dim lr As Long
Dim i As Long
lr = Range("AA65536").End(xlUp).Row
For i = 1 To lr
If Cells(i, 27) <> "" Then Cells(i, 27).GoalSeek Goal:=44500, ChangingCell:=Cells(i, 26)
Next i

P.S. This is a good start for me. Thanks.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
trackman,

I'm not very good in theoretical approach of error messages...
Are you sure that Cells(i, 27) is always a formula? (but I don't know what it has to do with referencing)

a good thing to do is break up your code in little pieces and then pass through hitting F8 go to the different variables with your mouse: the actual value will be highlighted... Or place some Msgboxes

Dim lr As Long
Dim i As Long
lr = Range("AA65536").End(xlUp).Row
Msgbox lr
For i = 1 To lr
Cells(i, 27).Select 'no error ?
Cells(i, 26).Select
Msgbox Cells(i, 27) <> "" 'True or False
If Cells(i, 27) <> "" Then Cells(i, 27).GoalSeek Goal:=44500, ChangingCell:=Cells(i, 26)
Next i

I just tried out with a simple example. Protecting the worksheet you get Error 1004 Reference not valid (translated from dutch)
Is your sheet protected and are the cells to change locked?

kind regards,
Erik
 

SonicBoomGolf

Active Member
Joined
Aug 7, 2004
Messages
325
The first solution you gave worked. The problem was that i = 1 to lr. When I tried i = 2 to lr it worked. There was no data to analyze for Goal Seek in row 1. This was my first attempt at declaring a variable for looping. Thanks for the help!!!

This worked......

Dim lr As Long
Dim i As Long
lr = Range("AA65536").End(xlUp).Row
For i = 2 To lr
If Cells(i, 27) <> "" Then Cells(i, 27).GoalSeek Goal:=44500, ChangingCell:=Cells(i, 26)
Next i
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
YEH! Computers behave as silly as we tell them to do!
:rolleyes:
If we tell them to search for a mountain in our house, they will say: can't find a mountain. :oops: Most of the time they don't say: mountains can't be found in houses! o_O It would still be easier to debug. :wink:
 

Forum statistics

Threads
1,147,622
Messages
5,742,205
Members
423,711
Latest member
luisfreitas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top