Multiple GoalSeek macro: Runtime error '1004': Reference not valid.

[Matt_Clarke]

Hi Everyone, my first post here

1. Version of the program
2. What you want it to do
3. Cell references, bookmark names, column letters, row numbers, worksheets, styles, whatever pertains to the information at hand
4. Error messages if any
5. If not the entire code, then at least some of it
6. Sample data (before and after sample worksheets, add as attachments here)
7. Politeness and gratitude
8. Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.

1) Excel 2003
2)/3) Perform multiple goalseeks, using columns as Ranges for "Set value" (B2:B4), "Desired value" (A2:A4) and "Changing Cell" (C2:C4)
4) Runtime Error '1004': Reference not valid.
5) brettdj's Multiple GoalSeek Macro (very kindly made available, thank you).

The macro gets stuck in the Goal Seek For...Next loop:

' Loop through the goalseek method
For i = 1 To TargetVal.Columns.Count
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).Value, ChangingCell:=ChangeVal.Cells(i)
Next i

6) See attached sheet

Thank you very much anyone who can tell me what is going wrong here, why, and how to avoid this problem from continuing.

Matt

[Kafrin]

In your attached sheet there are no macros, the named ranges you mentioned above are not named, and there are no formulas, so there is nothing to use for goal seeking. This makes it quite difficult to help you.

1) Make sure you have named the ranges.
2) Make sure your formulas a re in place in the DesiredVal cells.

3) Try referring to the named ranges as:
ws.Range("TargetVal").Cells...
where ws is a Worksheet variable that has been set ot be the relevant worksheet.

[Matt_Clarke]

Hi Kafrin,

Thank you for taking a look. I had overlooked the need for formulas in the Desired Range and Target Range columns in that example spreadsheet (I made a quick copy of the original - here is a version that has business critical info removed so that for all intents and purposes we're just looking at numbers and formulas here).

Also included in a module is the Multiple Goalseek Macro that I'm trying to use according to its instructions - the instructions don't mention setting-up specific named ranges, only selecting ranges to be designated Desired Cell/Set Cell/Changing Cell on which the Goal seeks are performed. Please correct me if I have misunderstood.

One issue that occurs to me is that I want the Goalseeks performed row-by-row down the columns:

N14:N66 = Target Cell
M14:M66 = Set Cell
P14:P66 = Changing Cell

However, the Goalseek For...Next loop in the macro is set to count the ranges as columns.count which strikes me as odd, in that the instructions say that it doesn't matter if the ranges are in columns or else in rows.
But if our ranges are in columns (53 tall, 1 wide) then is the count method missing the majority of the range specified?

' Loop through the goalseek method
'Shouldn't this be TargetVal.Rows.Count if my ranges are 1 column-wide, 53 rows-tall?
For i = 1 To TargetVal.Columns.Count
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).Value, ChangingCell:=ChangeVal.Cells(i)
Next i

Again, thanks for taking the time to help me unravel what is going on, and thank you for being patient as I get to grips with posting (let me know if there's more info you might need).

Matt

[Kafrin]

You don't seem to have attached the file.

You're correct that if your data is arranged in columns you need to work down the rows, so yes I think you should change the For loop to look at TargetVal.Rows.Count

I realised reading your second post that the named ranges thing was me misinterpreting what you were doing. Presumably you are defining the range names and then setting them equal to the ranges, ie:


Dim TargetVal As Range, DesiredVal As Range, ChangeVal As Range

Set TargetVal = [worksheet reference].Range("N14:N66")
Set DesiredVal = [worksheet reference].Range("M14:M66")
Set ChangeVal = [worksheet reference].Range("P14:P66")

If this is the case then please ignore my comments about naming ranges.