Newton Excel Bach, not (just) an Excel Blog

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