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
    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
  •