[MS Excel] automate a calculation

[Dormilich]

Hi there,

I somehow managed to create a Makro in Excel and it does what it is supposed to do (yeah!): do a goal seek.
what I want next is that it does this goal seek not only in one row (the currently specified cells) but in all rows of the concerned column (I want to create a graph from the calculated values), i.e. a Range() is certainly involved. but I have no idea where to look how to do a goal seek on a range. can you help me find that out?

current version of the code:

' (re)calculate value when a parameter is changed
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' H and A are the columns of interest
[H9].GoalSeek Goal:=[D4], ChangingCell:=[A9]

End Sub

PS. I’m not a VBA coder and I do not plan to become one.

[zmbd]

Dormilich:
Do I understand correctly:
Say you have column A
Starting in row 1
You do a goal seek on the value in A1
then you want to move done and use the value in A2 for the goal seek
A3... run GS
A4... run again
etc....

If so then all we need to do is either use the offset method to move the active cell from the current to the next, or at least get that value from the cell, and get GS to run for you using the new value.

If however, you want to use the values in the range A1....A# in the GS at the SAME time, that is not possible with that function.
( Use Goal Seek to find the result you want by adjusting an input value (...)
- Goal Seek works only with one variable input value. If you want to accept more than one input value; for example, both the loan amount and the monthly payment amount for a loan, you use the Solver add-in. For more information about the Solver add-in, follow the links in the See Also section.

[Dormilich]

Starting in row 1
You do a goal seek on the value in A1
then you want to move done and use the value in A2 for the goal seek
A3... run GS
A4... run again
etc....

exactly, that’s what I want. though both the cells for GoalSeek and ChangingCell move forward.

If however, you want to use the values in the range A1....A# in the GS at the SAME time, that is not possible with that function.

nope, that would not work with a standard goal seek. (and I do not intend to do that).

If so then all we need to do is either use the offset method to move the active cell from the current to the next, or at least get that value from the cell, and get GS to run for you using the new value.

the problem with that is that the changing cell *and* the target cells need to move forward.
so I need to look for a method that returns the number (or whatever) of the current cell of the range and determine the target cells for the GS.
maybe something along ...?

' not VBA code, I know
For Each Cell in Range_1
    position = Cell.indexInRange
    Range_1(position).GoalSeek Goal:=[D4], ChangingCell:=Range_2(position)

[zmbd]

is [D4] changing too?

You can use ActiveCell.Offs et(Row,Col)(
and increment the row,col indexs to point to your cells.

The only cavets is that you need to select the correct cell first and you'll need to test for empty/null values.

So say you have the target value in Column A
The formula in Column B
and you need the correct value in Column c
Click on A1 first and your formula for the first round would be:
ActiveCell.Offs et(0, 1).GoalSeek goal:=ActiveCel l.Offset(0, 0), ChangingCell:=A ctiveCell.Offse t(0, 2)
Now just offset your row index, check for values.

[Dormilich]

is [D4] changing too?

no, it stays constant for all Goal Seeks.

The only cavets is that you need to select the correct cell first and you'll need to test for empty/null values.

that is a real caveat, as the Makro is (currently) triggered by a change in the worksheet (e.g. by giving D4 a new value manually). further, the range of cells for the GS does not change.




PS. if you like some background: I’m doing a calculation of a VLE (Vapour Liquid Equilibrium) where the GoalCell is the system pressure (as sum of the Vapour Pressures) and the ChangingCell is the Temperature. the vapour pressure is dependent on the temperature, but the foumula is that complex (I’m using the Wagner equation in combination with the Raoult-Dalton law), that I can only find the Temperature by iteration, which I use the GoalSeek for.

[zmbd]

In the case where the cell needs to be selected,

range("cell or range name").select
range("cell or range name for a specfic cell").activate

Modify the other code to hold [D4] as you had it or for a range as it will not need to be offset.

[Dormilich]

so I should use a For loop to address the cells of the ranges of interest?

[zmbd]

Either a "For" or "DO" loop will work.
With the "For" so long as you know the count of cells.
With the "Do" you'll need your own index and a check. I'd more than likely use this in that I could increment my offset, check for null in the equation cell, if isnull() then exit the loop else run GS.

LIke 10 ways to skin this Cat... so to speak.
Technically, you could even select the range of cells (and about 5 ways to do that ;-) ) and do a for...each...ne xt; however, that would involve yet another tweek to how to get the values.

[Dormilich]

quick question regrading the loop structure, how do I "construct" the cells in question (e.g. A50 and H50), do I need to use Range() for that?

PS. do you recommend any reference besides MSDN?

[Dormilich]

would be something like the following possible*?

' the range is always fixed (0% ... 100%)
For Each c in Range("H11:H110")
  ' unless deleted by mistake, both cells (Ax, Hx)
  ' have the appropriate content
  c.GoalSeek Goal:=[D4], ChangingCell:=c.Offset(0, -7)
Next

* - don’t have MS Excel at home, so I cannot test it

[zmbd]

For the select:
'For Cell "A1" range("A1").sel ect
'For a group of cells range("A1:B3"). select
'For a group of cells with a name range("TheNameI s").select

For the activate, same concept as above; however, you can only do a single cell address ( "A1" not "A1:B3" nor can you use a range name that refers to a group of cells; however, you can use a range name that refers to a single cell)

Also, in an activate, if you select a group of cells first and then use the activate method the cell must be in the range of the selected cell group or the cells are unselected.

... in fact, it just dawned on me, you could probably get away with just doing the Range("A1").Act ivate without the select first. Just a bad habit I have of selecting first and then activating the cell.

You can [F1] in the vba for a little more info on this entire thing.

[zmbd]

Dormilich:
I actually have Excel2010 sitting on the desktop open for another project here at the house...

You need to Dim C as Range
SO I debuged the code, compiled, and setup a dummy sheet to run this on... your code ran just fine.
:-)

[Dormilich]

SO I debuged the code, compiled, and setup a dummy sheet to run this on... your code ran just fine.

the power of imagination ... (and a little reference look-up)

you’ll get a status report on monday when I can test it in Excel.

[Dormilich]

Monday’s status report: it works as expected.

thanks, zmbd