Macro to perform calculations
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Macro to perform calculations

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 )

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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/viewtop...2496&forum=2&8

    [ This Message was edited by: Cosmos75 on 2002-04-02 13:19 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com