Using Loop in Goal Seek

[SonicBoomGolf]

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
	T	U	V	W	X	Y	Z	AA
1	NumberofChepPallets	Avg.CHEPWeight	TotalPalletWeight	TotalPalletWeightPlusProductWeight	ExcessWeight	AveWeight/Pallet	NumberofChepPallets	TotalWeight
2	25	65	1625	72393	27,893	2,831	15	44,500
3	29	65	1885	49713	5,213	1,649	29	49,713
4	29	65	1885	48397	3,897	1,604	29	48,397
5	29	65	1885	48247	3,747	1,599	29	48,247
6	27	65	1755	48135	3,635	1,718	27	48,135
7	27	65	1755	48046	3,546	1,714	27	48,046
8	27	65	1755	47949	3,449	1,711	27	47,949
9	26	65	1690	47915	3,415	1,778	26	47,915
10	25	65	1625	47894	3,394	1,851	25	47,894
11	27	65	1755	47889	3,389	1,709	27	47,889
12	27	65	1755	47856	3,356	1,707	27	47,856
Sylvester

 

[erik.van.geit]

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:

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]

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]

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]

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]

YEH! Computers behave as silly as we tell them to do!

If we tell them to search for a mountain in our house, they will say: can't find a mountain. Most of the time they don't say: mountains can't be found in houses! It would still be easier to debug. :wink: