Understanding brettdj multiple goal seek code

[fred3]

I'm capable of programming but not VBA. So, I'm starting to learn out of necessity. I got the brettdj code for doing multiple goal seeks and decrypted the terminology for the inputs.

I changed the cell references to match the sheet I'm working with and that seems to have worked fine. BUT the thing errors out. I don't know how to debug VBA. I'm getting the impression that maybe this has to do with naming ranges and I've not named any ranges unless it happens in the code.

I think all I need is a push in the right direction....

All of the cells are arranged in rows. So that's different from the example sheet where one set of cells is in a column.

Thanks

[CatDaddy]

what code are you using?

[Bob Phillips]

You don't need to change anything as far as I can see. It prompts you with a suggested range, but you can select another range regardless.

I just ran it and it ran fine. Whereabouts does it error, which line?

[fred3]

Run time error '1004'
Reference is not valid.

In debug:
The line near the bottom is highlighted:
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).Value, ChangingCell:=ChangeVal.Cells(i)

Thanks

[Bob Phillips]

That was a bit obvious in hindsight I guess Can you post the workbook and let's see it.

[fred3]

Here it is:

Open Sheet Summary2 and run the Macro. It defaults to the intended cell ranges and you can see them as the dialogs come up.

[Bob Phillips]

You have a hidden column S which is largely empty. Your DesiredVal range is R8:BD8, when it processes S8 it fails because the desired Goal is 0.

[fred3]

Thank you! The hidden columns were supposed to have "dummy" operations so that this wouldn't happen. But, col S didn't get that treatment.

So how did you find col S as the culprit? Anything useful for a VBA learner?

[Bob Phillips]

When it errored I just checked the value of the loop counter. Looking at the goal seek cell, it had a value of zero, I guessed Goal Seek doesn't like to seek to 0. The loop counter was 2, and the data started in column R, it had to be column S, and when I looked at the spreadsheet, it was hidden. It all pointed just one way ...

[brettdj]

Having seen an SO Question that referred to this Article I note that

For i = 1 To TargetVal.Columns.Count

should have been

For i = 1 To TargetVal.Cells.Count

[swiftninja]

brettdj

Really appreciate your contributions to this forum, it has been really helpful.

Anyways, I could use your help on your multi-goal seek code.

I am using your multi-goal seek to calculate a sales price based on an ROI percentage goal.

So for instance, if I have 4 columns:
Cost, Sale Price, ROI Goal, ROI Result

and my ROI goal for a bunch of cells is 30%, then goal seek would return the sales price value for each cell to meet that ROI goal.

However, if for some cells, an ROI RESULT value is not present because there is no cost inputted in that specific row, then goal seek understandably returns an error.

I need a way to handle those errors by returning a string in the sales price cell "Input Cost" and move on to the next goal seek loop.

This is what I proposed but it has one small hiccup - it always puts the string "Input Cost" in the sales price cell that is after the last cell in the TargetVal.Cells Range.

InputCost:
ChangeVal.Cells(i) = "Input Cost"
Resume Next


Exit Sub

[SamT]

Band aid for logic anomaly:

InputCost: 
ChangeVal.Cells(i) = "Input Cost" 
If i = TargetVal.Cells.Count Then Exit Sub

Resume Next 
Exit Sub

[swiftninja]

Thanks for the help SamT. But it didn't quite work for me. Instead, nothing was processed and the procedure just exits.

Here is the whole code for reference, credit to brettdj:

Sub MultiGoalSeek()
Dim TargetVal As Range, DesiredVal As Range, ChangeVal As Range, CVcheck As Range
Dim CheckLen As Long, i As Long
restart:
With Application
.Iteration = True
.MaxIterations = 500
.MaxChange = 0.0000000000001
On Error GoTo CancelMethod
Set TargetVal = .InputBox(Title:="Select a column range", _
prompt:="Select Resulting ROI Column Range", Type:=8)
Set DesiredVal = .InputBox(Title:="Select a column range", _
prompt:="Select the Column Range of the ROI or Net Profit GOAL", Type:=8)
Set ChangeVal = .InputBox(Title:="Select a column range", _
prompt:="Select Sale Price or Cost Price Column Range That You Wish To Calculate", Type:=8)
End With
'Ensure that the amount of cells is consistent
If TargetVal.Cells.Count <> DesiredVal.Cells.Count Or TargetVal.Cells.Count <> ChangeVal.Cells.Count Then
CheckLen = MsgBox("Ranges were different lengths, please press yes to re-enter", vbYesNo + vbCritical)
If CheckLen = vbYes Then
'If ranges are different sizes and user wants to redo then restart code
GoTo restart
Else
Exit Sub
End If
End If
CancelMethod:
Exit Sub
' Loop through the goalseek method
On Error GoTo InputCost
For i = 1 To TargetVal.Rows.Count
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).value, ChangingCell:=ChangeVal.Cells(i)
Next i
InputCost:
ChangeVal.Cells(i) = "Input Cost"
If i = TargetVal.Cells.Count Then Exit Sub
 Resume Next
Exit Sub
End Sub

[SamT]

It worked before you added that line and now it doesn't?

[swiftninja]

Correct. It doesn't goal seek any cell, it just displays the input boxes and after selecting all the ranges it exits.

[SamT]

Remove that line and try it on the same worksheet.

[swiftninja]

It works if I remove that line.

It even works if I take away the IF statement and leave just this, and it puts "Input Cost" into every target value cell that has an invalid ROI due to it dividing by No Cost, but it always puts "Input Cost" into the cell following the last target cell in the range, which is weird because that cell wasn't even selected in the range:

InputCost: 
    ChangeVal.Cells(i) = "Input Cost" 
     
    Resume Next 
    Exit Sub

[SamT]

First try Moving the Label "CancelMethod:" to just above the End Sub line in the original as posted. Even if nothing changes, leave it there. I am surprised that the goal seeking is ever run because that Exit Sub is always encountered by the program flow.


Then try

InputCost: 
    On Error GoTo 0
    ChangeVal.Cells(i) = "Input Cost" 
    Next i

Finally, try

    On Error GoTo 0
    If i = TargetVal.Cells.Count Then Exit Sub 
    ChangeVal.Cells(i) = "Input Cost" 
    Next i

[swiftninja]

I'm getting for both "Compile Error: Next without For"

CancelMethod: Placement doesn't make any discernible difference.

[SamT]

,

Post the code that gives those two errors.