Change one (or more than one) variable and have other variab

[Cosmos75]

I want to make a macro. I have 8 variables (x1 to x8) that I want to be able to add up to a constant value by changing one or even more then one but not the others.

E.g. (x1*1.1)+ (x2*1.2)+ (x3*1.3)+ (x4*1.4)+ (x5*1.5)+ (x6*1.6)+ (x7*1.7)+ (x8*1.=120

I have the Variable (x1 to x8) listed in Column A and the multipliers (1.1 to 1. in Column B.

How can I change x1 have the other change appropriately? Or change a few variables and have the others change appropriately?

Each variable has a lower limit that they cannot go under and an upper limit that cannot go over. For example, x5 has to be between 5 and 40.

Also, each variable cannot be a negative number.

Is there I way I can do this?

FANTASY REQUEST:
One more request, although this certainly isn’t necessary. Is there anyway to add a scroll-bar to each variable, whereby if I move one scroll-bar, the others move accordingly? Just curious to see if it could be done. (A scroll-bar from the forms tool-box where you can link a cell to it and move the “scroller” or click the arrows on the scroll-bar to change the value in the cell in specified increments and with a lower and upper limit is what I mean.)

Note: That smiley face is 8 )

[ This Message was edited by: Cosmos75 on 2002-03-15 15:48 ]

[Damon Ostrander]

Hi Cosmos75,

Yes, you can do all of this. I take it that you want to be able to edit any one of the variables and have all the others automatically calculate to satisfy the equation. Of course, this cannot be done with cell formulae because the instant you enter a value in a cell with a formula it will wipe out the formula. It has to be done via worksheet events, namely the Change event. With the Change event you can determine (using the Target argument) which of the variables (cells) has been edited, and recalculate the others. Of course, you must have the algorithm to do this, and I assume you do. The mathematical problem here is that it appears there will be many solutions to the equation, not just one, especially if the variables do not have to be integers. But again, I assume you have all that worked out.

The one part that is questionable is limiting the range of the variables. What if there is no solution unless the solution for one of the variables goes outside its range? What would you want the program to do in this case? The easiest thing would be just to show a message saying there is no solution within the variable range, and set the entry cell back to its original value.

Regarding using the spinners to change the values, this is quite easy, especially if the variables are integer values. Just assign the spinner value (which is an integer that goes from the spinner's Min property value to its Max property value) to the variable, textbox, cell, or wherever you want to put it. If the variable is not an integer you will have to decide on a fixed step size by which the variable will change with each step of the spinner since it always steps by one. So, for example, if you want it to step by 0.25 you would multiply the spinner's Value property by 0.25 before assigning it to the variable, textbox, or cell that corresponds with that variable.

I hope this is helpful.

[Cosmos75]

I am afriad I don't know what a Change Event is or even what a worksheet event is??

Is that an add-in for Excel??

[Damon Ostrander]

Hi again Cosmos75,

These events are Excel features that can be accessed via Visual Basic for Applications (VBA) code. The Change event, for example, enables one to write VBA code that does something whenever the value of a cell is manually changed. So, instead of using a formula to modify cell B1 whenever A1 changes, you could using VBA code calculate the valueof B1 when A1 changes, but at the same time the code could also calculate the value of A1 if B1 changes. This is something you can't do with a formula. I could send you an example if you like.

[Cosmos75]

Sure, it would be extremely helpfull. Would it contain examples of formulas to add things?

Could included a simple Average,Max, and Sum Funtions syntax?

There is one thing that came to mind. If I wanted to add the Sum of a list in Column A or determin the Max and have the sum/max put at the bottom of the list, would the Event keep on running since a value (sum/max) is added to the end of the list? An Event is recursive, correct? [Did some research online about Events]

Thanks for your help!

[Damon Ostrander]

Hi again Cosmos75,

Yes, you can do Max, Avg, Sum functions this way, but not using in-cell functions. It's a bit hard to explain, but easy to demonstrate.

Regarding recursion, this is a common problem for those implementing events for the first time, but it is actually easily handled. Unfortunately the Excel help files do not provide a warning on this, or any explanation of how to get around it, and this is probably the main reason why so many users struggle with it. I have put numerous postings on the board in the past showing how to eliminate this problem.

Damon

[NateO]

Also, of less importance, you can check "Disable Smilies on this Post" at the bottom of this form to show things like 8) or

[Cosmos75]

On 2002-03-19 12:38, Damon Ostrander wrote:
Hi again Cosmos75,

Yes, you can do Max, Avg, Sum functions this way, but not using in-cell functions. It's a bit hard to explain, but easy to demonstrate.

Regarding recursion, this is a common problem for those implementing events for the first time, but it is actually easily handled. Unfortunately the Excel help files do not provide a warning on this, or any explanation of how to get around it, and this is probably the main reason why so many users struggle with it. I have put numerous postings on the board in the past showing how to eliminate this problem.

Damon

Cool. Although I have no knowledge of how to write code for Events. Any simple examples of Adding, Multiplying, Averageing, Max? Are choosing Ranges like that in VBA? Any good place for me to start?

[Cosmos75]

Finally got some code as a stating point, see link below.

http://www.mrexcel.com/board/viewtop...c=3782&forum=2