an excel challenge

[adolf hamster]

figured this might get a few heads scratching out there.

basically i have a spreadsheet with multiple sheets that are copies of each other with different calculation paremeters.

i have a cell on each sheet that needs to be manually set to obtain a known value in another cell, now excel does have this functionality in the goal seek command, but what i'd like is an automatic method to goal seek on all these sheets when other values are changed.

i've found this: http://excel.kingofmath.com/?p=267

and adjusting it to my sheet i have:

Private Sub CheckGoalSeek()
Static isWorking As Boolean

If Round(Range(“L4”).Value, 6) <> Range("L3") And Not isWorking Then
isWorking = True
Range(“B8”).Value = 0
Range(“L4”).GoalSeek Goal:=Range("L3"), ChangingCell:=Range(“B8”)
isWorking = False
End If
End Sub

where B8 is the static value to adjust, L3 is the goal value and goal output is L4

thing is, it's not triggering, and it's been a while since i've messed with macro's but it seems like it should be working even if the calculation is triggered from another sheet.

edit: so some debugging seems to suggest it thinks L4 is empty, despite L4 very definately not being empty as it contains the output formula for my calculation

 

[dazzerd]

Not entirely clear what you are trying to achieve but your method needs to be triggerable and added to the worksheet change or selection event of each sheet. You could still have a main sub and just have the change event in each sheet calling your main sub elsewhere. You other option depending on what you are doing is using a UDF which would trigger every time the sheet changed just like any other inbuilt function.

 

[adolf hamster]

yeah this is the problem, if i have the sub as a worksheet change event it won't accept any of the cell references.

i've sort of worked around it by recording a fixed macro to do all the sheets and having a reminder cell to tell folk to run it.

 

[dazzerd]

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

The parameter Target refers to the cell which instigated the change so you can grab that value if you want or you can pass target to a public sub somewhere else and test that.

You can also do interesting stuff like hold all the cells you want to monitor in a collection and drive the event off a change within the collection.