469,125 Members | 1,629 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,125 developers. It's quick & easy.

[MS Excel] automate a calculation

Dormilich
8,651 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. ' (re)calculate value when a parameter is changed
  2. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  3.  
  4. ' H and A are the columns of interest
  5. [H9].GoalSeek Goal:=[D4], ChangingCell:=[A9]
  6.  
  7. End Sub
PS. Iím not a VBA coder and I do not plan to become one.
Mar 21 '13 #1

✓ answered by 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...next; however, that would involve yet another tweek to how to get the values.

13 2204
zmbd
5,400 Expert Mod 4TB
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.
Mar 21 '13 #2
Dormilich
8,651 Expert Mod 8TB
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 ...?
Expand|Select|Wrap|Line Numbers
  1. ' not VBA code, I know
  2. For Each Cell in Range_1
  3.     position = Cell.indexInRange
  4.     Range_1(position).GoalSeek Goal:=[D4], ChangingCell:=Range_2(position)
Mar 21 '13 #3
zmbd
5,400 Expert Mod 4TB
is [D4] changing too?

You can use ActiveCell.Offset(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.Offset(0, 1).GoalSeek goal:=ActiveCell.Offset(0, 0), ChangingCell:=ActiveCell.Offset(0, 2)
Now just offset your row index, check for values.
Mar 21 '13 #4
Dormilich
8,651 Expert Mod 8TB
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.
Mar 21 '13 #5
zmbd
5,400 Expert Mod 4TB
In the case where the cell needs to be selected,
Expand|Select|Wrap|Line Numbers
  1. range("cell or range name").select
  2. range("cell or range name for a specfic cell").activate
  3.  
Modify the other code to hold [D4] as you had it or for a range as it will not need to be offset.
Mar 22 '13 #6
Dormilich
8,651 Expert Mod 8TB
so I should use a For loop to address the cells of the ranges of interest?
Mar 22 '13 #7
zmbd
5,400 Expert Mod 4TB
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...next; however, that would involve yet another tweek to how to get the values.
Mar 22 '13 #8
Dormilich
8,651 Expert Mod 8TB
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?
Mar 22 '13 #9
Dormilich
8,651 Expert Mod 8TB
would be something like the following possible*?
Expand|Select|Wrap|Line Numbers
  1. ' the range is always fixed (0% ... 100%)
  2. For Each c in Range("H11:H110")
  3.   ' unless deleted by mistake, both cells (Ax, Hx)
  4.   ' have the appropriate content
  5.   c.GoalSeek Goal:=[D4], ChangingCell:=c.Offset(0, -7)
  6. Next



* - don’t have MS Excel at home, so I cannot test it
Mar 22 '13 #10
zmbd
5,400 Expert Mod 4TB
For the select:
'For Cell "A1" range("A1").select
'For a group of cells range("A1:B3").select
'For a group of cells with a name range("TheNameIs").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").Activate 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.
Mar 22 '13 #11
zmbd
5,400 Expert Mod 4TB
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.
:-)
Mar 22 '13 #12
Dormilich
8,651 Expert Mod 8TB
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.
Mar 23 '13 #13
Dormilich
8,651 Expert Mod 8TB
Monday’s status report: it works as expected.

thanks, zmbd
Mar 25 '13 #14

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by kids_pro | last post: by
2 posts views Thread by Paul Remblance | last post: by
6 posts views Thread by jtswim01 | last post: by
8 posts views Thread by Fendi Baba | last post: by
5 posts views Thread by =?Utf-8?B?amVsbGU3OQ==?= | last post: by
2 posts views Thread by =?Utf-8?B?QWxleGFuZGVyIFd5a2Vs?= | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.