Results 1 to 3 of 3

Thread: x,y,z=100% - if x = 77, what value y and z...

  1. #1

    x,y,z=100% - if x = 77, what value y and z...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    hi
    math has never been my forte, never mind excel :-/
    the problem:
    i have to make a mixture of three substances that have to make 100% [per mass].
    substance x = 77% of the mixture
    substance y = 18% of the mixture
    substance z = 5% of the mixture
    total=100%
    i want to have/make a macro or run a vba or set up a sheet where i can enter values for x y or z and get the rest of the calculation done for me.
    any help will be more than welcome!
    in Christ
    gabriel smit

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,807
    Articles
    0
    Excel Version
    O365
    This code goes in the sheet code modulke and automatically updates whe A2, A3 or A4 is changed. It assumes A2 is x, A3 is y, and A4 is z

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
        On Error GoTo ws_exit
        
        Application.EnableEvents = False
        
        Select Case True
        
            Case Not Intersect(Target, Me.Range("A2")) Is Nothing
            
                Me.Range("A3").Value = Target.Value / 77 * 18
                Me.Range("A4").Value = Target.Value / 77 * 5
        
            Case Not Intersect(Target, Me.Range("A3")) Is Nothing
            
                Me.Range("A2").Value = Target.Value / 18 * 77
                Me.Range("A4").Value = Target.Value / 18 * 5
        
            Case Not Intersect(Target, Me.Range("A4")) Is Nothing
            
                Me.Range("A2").Value = Target.Value / 5 * 77
                Me.Range("A3").Value = Target.Value / 5 * 18
        End Select
    
    
    ws_exit:
        Application.EnableEvents = True
    End Sub

  3. #3
    thank you bob, it works 100%!
    yours in Christ Jesus,
    gabriel

    ps, how do i mark this as solved?

Posting Permissions

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