Goal seek - column reference

[al]

Sub Goalseek()
'

Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50")
Range("Y85").Select
Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51")
Range("Y86").Select
Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52")
End Sub

In above macro column Y can vary - i.e it E to X.
I therefore would like to have the column matched against
corresponding Numbers
e.g 1 would be equivalent to column E, 2 would be equivalent to column
F & so on.
I have cell AL83 which gives the required column "number"

How can i amend the above macro to link it with the value in cell AL83
& proceed with the goal seek

Thxs

 

[Bernie Deitrick]

Al,

Basically, you need to replace Range("CellAddress") with Cells(Row#, Col#)
and pick up the column number by reading the value of cell AL83

For example, you can replace Range("Y84")

with

Cells(84, Range("AL83").Value + 4)

So your final code might look like this:

Cells(84, Range("AL83").Value + 4).GoalSeek Goal:=Range("AL84"),
ChangingCell:=Cells(50, Range("AL83").Value + 4)

Not sure if your Goal changes each time or not....

HTH,
Bernie
MS Excel MVP

 

[al]

Al,

Basically, you need to replace Range("CellAddress") with Cells(Row#, Col#)
and pick up the column number by reading the value of cell AL83

For example, you can replace Range("Y84")

with

Cells(84, Range("AL83").Value + 4)

So your final code might look like this:

Cells(84, Range("AL83").Value + 4).GoalSeek Goal:=Range("AL84"),
ChangingCell:=Cells(50, Range("AL83").Value + 4)

Not sure if your Goal changes each time or not....

HTH,
Bernie
MS Excel MVP

Have change the macro as you suggested - but it does not work - can
you or someone else help pls

Sub Goalseekoffset()
'
Cells(84, Range("AL83").Value + 4).Goalseek Goal:=Range("AL84"),
ChangingCell:=Cells(50, Range("AL83").Value + 4)

Cells(85, Range("AL83").Value + 4).Goalseek Goal:=Range("AL85"),
ChangingCell:=Cells(51, Range("AL83").Value + 4)

Cells(86, Range("AL83").Value + 4).Goalseek Goal:=Range("AL86"),
ChangingCell:=Cells(52, Range("AL83").Value + 4)

End Sub

 

[al]

Have change the macro as you suggested - but it does not work - can
you or someone else help pls

Sub Goalseekoffset()
'
Cells(84, Range("AL83").Value + 4).Goalseek Goal:=Range("AL84"),
ChangingCell:=Cells(50, Range("AL83").Value + 4)

Cells(85, Range("AL83").Value + 4).Goalseek Goal:=Range("AL85"),
ChangingCell:=Cells(51, Range("AL83").Value + 4)

Cells(86, Range("AL83").Value + 4).Goalseek Goal:=Range("AL86"),
ChangingCell:=Cells(52, Range("AL83").Value + 4)

End Sub

sorry pals - it's working - got the input wrong in cell AL83 - thxs