PDA

View Full Version : Understanding brettdj multiple goal seek code



fred3
05-22-2012, 09:35 AM
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
05-22-2012, 09:49 AM
what code are you using?

Bob Phillips
05-22-2012, 10:17 AM
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
05-22-2012, 10:26 AM
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
05-22-2012, 10:31 AM
That was a bit obvious in hindsight I guess :doh: Can you post the workbook and let's see it.

fred3
05-22-2012, 12:54 PM
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
05-22-2012, 03:11 PM
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
05-23-2012, 09:59 AM
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
05-23-2012, 12:20 PM
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
09-28-2013, 11:33 PM
Having seen an SO Question (http://stackoverflow.com/questions/17475384/excel-multiple-goal-seek-macro-code-error/17482347#17482347) 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
09-13-2014, 06:35 PM
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
09-13-2014, 08:31 PM
Band aid for logic anomaly:

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

Resume Next
Exit Sub

swiftninja
09-13-2014, 08:51 PM
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
09-14-2014, 07:02 AM
It worked before you added that line and now it doesn't?

swiftninja
09-14-2014, 07:28 AM
Correct. It doesn't goal seek any cell, it just displays the input boxes and after selecting all the ranges it exits.

SamT
09-14-2014, 08:05 AM
Remove that line and try it on the same worksheet.

swiftninja
09-14-2014, 09:50 AM
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
09-14-2014, 10:29 AM
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. :dunno


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
09-14-2014, 02:21 PM
I'm getting for both "Compile Error: Next without For"

CancelMethod: Placement doesn't make any discernible difference.

SamT
09-14-2014, 03:01 PM
:dunno,

Post the code that gives those two errors.

swiftninja
09-14-2014, 03:20 PM
Private 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
Application.ScreenUpdating = False
MsgBox ("Please Wait.....It May Take A Couple of Minutes Depending On The Amount of Items You Processed")
'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
' Loop through the goalseek method
On Error GoTo InputCost
For i = 1 To TargetVal.Cells.Count
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).value, ChangingCell:=ChangeVal.Cells(i)
Next i
InputCost:
On Error GoTo 0
ChangeVal.Cells(i) = "Input Cost"
Next i
Application.ScreenUpdating = True
CancelMethod:
Exit Sub
End Sub

SamT
09-14-2014, 05:14 PM
For i = 1 To TargetVal.Cells.Count
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).Value, ChangingCell:=ChangeVal.Cells(i)
Iterate:
Next i
InputCost:
On Error GoTo 0
ChangeVal.Cells(i) = "Input Cost"
GoTo Iterate
CancelMethod:
Application.ScreenUpdating = True
End Sub

A little different logic

' Loop through the goalseek method
For i = 1 To TargetVal.Cells.Count
On Error Resume Next
TargetVal.Cells(i).GoalSeek Goal:=DesiredVal.Cells(i).Value, ChangingCell:=ChangeVal.Cells(i)
If Err <> 0 Then ChangeVal.Cells(i) = "Input Cost"
Err = 0
Next i
CancelMethod:
Application.ScreenUpdating = True
End Sub

swiftninja
09-14-2014, 06:58 PM
Wow awesome, the 2nd one works! Thank you.

Hey if you feel up to it, would you know how to calculate the progress in the mentioned procedure? I have a progress bar ready to implement with this but I'm just missing a way to calculate the percent done in this multigoalseek.

SamT
09-14-2014, 07:33 PM
Progress Bar %done = (100 / TargetVal.Cells.Count) * i

swiftninja
09-14-2014, 08:55 PM
Wow thank you so much SamT. You've been extremely helpful.

PS. Feel free to edit your last post to change "TargetValue" to "TargetVal" for the benefit of others who come across this.

SamT
09-15-2014, 03:53 PM
you just told them. :rofl: