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

Cosmos75

Active Member
Joined
Feb 28, 2002
Messages
359
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.:cool:=120

I have the Variable (x1 to x8) listed in Column A and the multipliers (1.1 to 1.:cool: 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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
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??
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
Also, of less importance, you can check "Disable Smilies on this Post" at the bottom of this form to show things like 8) or :)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top