Macro to perform calculations

[Cosmos75]

I want to make a macro or use excel event. 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.8 )=120

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

If someone could get me started with
1) How the code to add, subtract, multiply, divide
2) What to look out for when changing several variables out of the eight
3) How to set the limits.
I've had some replies to this in the past but I don't know how to write code for events and my VBA code knowledge is limited to selecting ranges and copy/paste stuff.

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 )

 

[davers5]

This is an interesting question. I just wrote this code to attempt to do what you just asked.

Private Sub Worksheet_Change(ByVal Target As Range)
'delcare variables
Dim a As Single
Dim b As Single
Dim c As Single

If ActiveCell.Column = 1 Then
'assign values to variables
a = ActiveCell.Value
b = ActiveCell.Offset(0, 1).Value
c = ActiveCell.Offset(0, 2).Value

'do the calculation
If a * b <> c Then
b = c / a
ActiveCell.Offset(0, 1).Value = b
End If
ElseIf ActiveCell.Column = 2 Then
a = ActiveCell.Offset(0, -1).Value
b = ActiveCell.Value
c = ActiveCell.Offset(0, 1).Value

If a * b <> c Then
a = c / b
ActiveCell.Offset(0, -1).Value = a
End If
ElseIf ActiveCell.Column = 3 Then
c = ActiveCell.Value
Else
Cells(ActiveCell.Row, 1).Select
End If
End Sub

The key to this is that you have to change your settings under the tools-options menu, edit tab. Make sure the box titled move selection after enter is NOT CHECKED. If you change a value in column a or b and hit enter, c will be affected. It works with the worksheet change event.

Hope this helps a bit at least.

Dave

I don't really like this code and I think there are better ways for this to be done, this was my first attempt and it worked ok, but it should give you an idea of how to do such a thing in VBA.

Dave

 

[Cosmos75]

Thanks!! It gives me a starting point. If I ever get the code to work as I want it to, I'll post it here.

THANK YOU!!! Posted this a few times but no one gave me any code as a starting point, although someone told me that it could be done using events. But I have no idea how to write events so your code is a great starting point. Who knows, maybe I'll figure out how to use a macro to do it on command.

 

[Cosmos75]

Here's the discussion about using events from another post of mine that starts out the same as this one. No code there, though.

http://www.mrexcel.com/board/viewtopic.php?topic=2496&forum=2&8
This message was edited by Cosmos75 on 2002-04-02 13:19

 

[davers5]

Another good way to learn come code is to record a macro and look at the code that excel generates for it. Once you record it, the code will be in the VB editor. It doesn't do anything fancy (i've never seen a variable created or anything), but it does the basics.

Good luck,

Dave

For a command button the event is a click event and it's syntax is:

Sub CommmandButton1_Click()
End Sub