30/01/2013 8:23 pm
Following a comment on the Using Goal Seek on Multiple Cells post, I have modified the macro so that the goal-seek ranges can be on a different sheet to the named input ranges. The new file can be downloaded from: GSeek.zip.
The new code is shown below, but the macro can be used as is; just enter the cell addresses where you want Goal Seek to work in the shaded cells (and sheet names, if not working on Sheet1), then press Alt-F8 and select GSeekA:
Sub GSeekA()
Dim ARange As Range, TRange As Range, Aaddr As String, Taddr As String, NumEq As Long, i As Long
Dim TSheet As String, ASheet As String
Dim GVal As Double, Acell As Range, TCell As Range, Orient As String
' Create the following names in the back-solver worksheet:
' Taddr - Cell with the address of the target range
' Aaddr - Cell with the address of the range to be adjusted
' gval - the "goal" value
' To reference ranges on different sheets also add:
' TSheet - Cell with the sheet name of the target range
' ASheet - Cell with the sheet name of the range to be adjusted
Aaddr = Range("aaddr").Value
Taddr = Range("taddr").Value
On Error GoTo NoSheetNames
ASheet = Range("asheet").Value
TSheet = Range("tsheet").Value
NoSheetNames:
On Error GoTo ExitSub
If ASheet = Empty Or TSheet = Empty Then
Set ARange = Range(Aaddr)
Set TRange = Range(Taddr)
Else
Set ARange = Worksheets(ASheet).Range(Aaddr)
Set TRange = Worksheets(TSheet).Range(Taddr)
End If
NumEq = ARange.Rows.Count
If NumEq = 1 Then
NumEq = ARange.Columns.Count
Orient = "H"
Else
Orient = "V"
End If
GVal = Range("gval").Value
For i = 1 To NumEq
If Orient = "V" Then
TRange.Cells(i, 1).GoalSeek Goal:=GVal, ChangingCell:=ARange.Cells(i, 1)
Else
TRange.Cells(1, i).GoalSeek Goal:=GVal, ChangingCell:=ARange.Cells(1, i)
End If
Next i
ExitSub:
End Sub
Posted by dougaj4
Tags: Excel, Goal Seek, Quartic, VBA
Mobile Site | Full Site
Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.
This macro for some reason isn’t working for me at all. Nothing happens when I try and set values to 80 for instance.
LikeLike
By PsDuCiel on 13/03/2014 at 1:22 am
What version of Excel are you using?
What happens when you press Alt-F8 to run the macro?
LikeLike
By dougaj4 on 13/03/2014 at 8:25 am
I am using Excel for Mac 2011, and I am finding that the Macro will not loop through the cells. I stipulate the range I24:I37, but it stops at I24 (no error)
LikeLike
By pmelton on 06/06/2016 at 5:57 pm
It’s difficult for me to solve problems that only occur on Macs, because I don’t have a Mac.
Do you know how to step through a macro? Can you tell me the code line it stops at?
LikeLike
By dougaj4 on 06/06/2016 at 10:18 pm