Goal Seek Macro for Multiple Sheets

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

    On Error GoTo ExitSub
    If ASheet = Empty Or TSheet = Empty Then
        Set ARange = Range(Aaddr)
        Set TRange = Range(Taddr)
        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"
        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)
            TRange.Cells(1, i).GoalSeek Goal:=GVal, ChangingCell:=ARange.Cells(1, i)
        End If
    Next i
End Sub

This entry was posted in Excel, VBA and tagged , , , . Bookmark the permalink.

4 Responses to Goal Seek Macro for Multiple Sheets

  1. PsDuCiel says:

    This macro for some reason isn’t working for me at all. Nothing happens when I try and set values to 80 for instance.


  2. pmelton says:

    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)


    • dougaj4 says:

      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?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.