Multiple Goal Seek with value range

[Alexee011]

Hi, I have a table that I have simplified below:

[TABLE="width: 672"]
[TR]
[TD][/TD]
[TD]01-Jan-13[/TD]
[TD]02-Jan-13[/TD]
[TD]03-Jan-13[/TD]
[TD]04-Jan-13[/TD]
[TD]05-Jan-13[/TD]
[TD]06-Jan-13[/TD]
[TD]07-Jan-13[/TD]
[/TR]
[TR]
[TD]Opening Balance[/TD]
[TD="align: right"]50,000[/TD]
[TD="align: right"]165,695[/TD]
[TD="align: right"]4,109[/TD]
[TD="align: right"]-145,891[/TD]
[TD="align: right"]-400,672[/TD]
[TD="align: right"]-441,662[/TD]
[TD="align: right"]-541,662[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Income[/TD]
[TD="align: right"]125,695[/TD]
[TD="align: right"]258,414[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]328,884[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expenses[/TD]
[TD="align: right"]150,000[/TD]
[TD="align: right"]420,000[/TD]
[TD="align: right"]150,000[/TD]
[TD="align: right"]254,781[/TD]
[TD="align: right"]369,874[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]258,741[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Drawdown[/TD]
[TD="align: right"]140,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Closing Balance[/TD]
[TD="align: right"]165,695[/TD]
[TD="align: right"]4,109[/TD]
[TD="align: right"]-145,891[/TD]
[TD="align: right"]-400,672[/TD]
[TD="align: right"]-441,662[/TD]
[TD="align: right"]-541,662[/TD]
[TD="align: right"]-800,403[/TD]
[/TR]
[/TABLE]


Closing balance = Opening Balance + Income - Expenses + Drawdown. My goal every day is to have a closing balance between $15,000 and $25,000 by a drawdown in multiple of $10,000. For example, on 2-Jan13, I would have to drawdown $20,000 to achieve a closing balance of $24,109 (between $15,000 and $25,000). Also, if the closing balance is more than $25,000, then Drawdown is zero.

As I have hundreds of lines to change, I came up with a macro that can do multiple "Goal Seek" with help from people who know VBA (I only know the basics). However, I was unable to come up with a solution that would allow me to solve my issue with the value of $15k to $25k by "changing the cell" in multiples of $10k. Below is my macro:

Option Explicit
Sub Multi_Goal_Seek()
Dim TargetVal As Range, DesiredVal As Range, ChangeVal As Range, CVcheck As Range
Dim CheckLen As Long, i As Long

restart:
With Application
Set TargetVal = .InputBox(Title:="Select a range in a single row or column", _
prompt:="Select your range which contains the ""Set Cell"" range", Type:=8)
Set DesiredVal = .InputBox(Title:="Select a range in a single row or column", _
prompt:="Select the range which the ""Set Cells"" will be changed to", Type:=8)
Set ChangeVal = .InputBox(Title:="Select a range in a single row or column", _
prompt:="Select the range of cells that will be changed", Type:=8)
End With

Set CVcheck = Intersect(ChangeVal, Union(Sheets(ChangeVal.Parent.Name).Cells.SpecialCells(xlBlanks), Sheets(ChangeVal.Parent.Name).Cells.SpecialCells(xlConstants)))
If CVcheck Is Nothing Then
MsgBox "Changing value range contains no blank cells or values" & vbNewLine & _
"Goal seek only works if the cells to be changed are values, please ensure that this is the case", vbCritical
Application.Goto reference:=DesiredVal
Exit Sub
Else

If CVcheck.Cells.Count <> DesiredVal.Cells.Count Then
MsgBox "Changing value range contains formulas" & vbNewLine & _
"Goal seek only works if the cells to be changed are values, please ensure that this is the case", vbCritical
Application.Goto reference:=DesiredVal
Exit Sub
End If
End If

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
GoTo restart
Else
Exit Sub
End If
End If

' 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
End Sub

I would appreciate if somebody can let me know how can solve this issue. Thanks a lot!

 

[Jon von der Heyden]

*bump*

Hi Alex

Welcome to the forum.

You posted your question in the "About the board" forum which is a forum for questions about how this forum works etc. I have moved your question to the correct forum, "Excel Questions". You need to make sure you select the right forum in future otherwise you probably won't get your question in front of the right people and you won't get a reply.

Regards
Jon

 

[Alexee011]

Oops, thanks Jon.

Alex

 

[jnospy]

Strong 7 year bump.

I have the same issue. Would appreciate a response.

Thanks