Percentage Calculation Between Two Cells Using Code in VBA

I have a set of cells that when combined need to equal 100%
As an example:
Considering cells B1 and B5

If I enter 55% in cell B1 then cell B5 automatically shows 45%

If I enter 25% in cell B5 then cell B1 automatically shows 75%


Thanks,

Edwin
gixxer1020Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try ( code to be placed in your worksheet module )

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not (Intersect(Target, Range("B1")) Is Nothing) Then
        Range("B5").Value = 1 - Range("B1").Value
    ElseIf Not (Intersect(Target, Range("B5")) Is Nothing) Then
        Range("B1").Value = 1 - Range("B5").Value
    End If
    Application.EnableEvents = True
End Sub

Open in new window


EDIT Disable Events

Regards
0
gowflowCommented:
pls try this attached workbook in sheet1 put any value in cell b1 and see result in b5 and vice versa. Also this handles negatives if you put -2 you get -98 also it handles the formatting of the cell as previous code proposed assumes that your cell is formatted as percentage while if it is not you can get weired values as subtracting 1 from any number would not work as is 1 is in percentage.

Let me know
gowflow
percentage.xls
0
gixxer1020Author Commented:
Thanks Rgonzo,

That works great...not sure if it's proper to ask another question to build on this but how would I expand that to dividing 3 and 4 ways?

Thanks,

Edwin
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

gowflowCommented:
gixxer1020
Did you try my solution ?? what do oyu mean dividing 3 and 4 way ? like have 3 cells and 4 cells that all would add to 100% ???

gowflow
0
gixxer1020Author Commented:
Hi gowflow,

No I have not tried your code yet.
And, yes I may also need to have 3 or 4 cells that would all add to 100%

Thanks,

Edwin
0
gowflowCommented:
ok just saw your post now and willing to help you but need some clarifications.

In your original post you had 2 cells and when 1 has a value the second has the complement to 100 that's fine.

Now we are talking about 4 cells say the following scenario:
Cell A1 = 70
Cell A2 blank
Cell A3 blank
Cell A4 blank

or any other combination
question:
Do you have a priority where to put the 30% or you want that 30% to be split equally in the 3 remaining cells or ... ??? pls clarify the process that you need the answer to be laided out.

gowflow
0
gowflowCommented:
Check this sample file what it does is any amount that you put in any cell will split the balance to 100% in the remaining 3 cells.

The cells concerned are A1,A2,A3,A4 and this can be modified depending on answering my previous request.

Note I have kept the routine for 2 cells as well it is in the sub Fill100In2Cells

here is the code for the new Sub for your easy reference all you need is to try the file and activate macros I have put in cell C4 the total of all A1,A2,A3 and A4 so it always add up tp 100%.

Sub Fill100In4Cells(target As Range)
    Dim SplitVal As Double
    
    Application.EnableEvents = False
    
    If Not (Intersect(target, Range("B1")) Is Nothing) Then
        Range("B1").NumberFormat = "0.00%"
        Range("B2").NumberFormat = "0.00%"
        Range("B3").NumberFormat = "0.00%"
        Range("B4").NumberFormat = "0.00%"
        If Range("B1").Value >= 0 Then
            SplitVal = 1 - Range("B1").Value
            Range("B2").Value = SplitVal / 3
            Range("B3").Value = SplitVal / 3
            Range("B4").Value = SplitVal / 3
        Else
            SplitVal = -Range("B1").Value - 1
            Range("B2").Value = SplitVal / 3
            Range("B3").Value = SplitVal / 3
            Range("B4").Value = SplitVal / 3
        End If
    Else
        If Not (Intersect(target, Range("B2")) Is Nothing) Then
            Range("B1").NumberFormat = "0.00%"
            Range("B2").NumberFormat = "0.00%"
            Range("B3").NumberFormat = "0.00%"
            Range("B4").NumberFormat = "0.00%"
            If Range("B2").Value >= 0 Then
                SplitVal = 1 - Range("B2").Value
                Range("B1").Value = SplitVal / 3
                Range("B3").Value = SplitVal / 3
                Range("B4").Value = SplitVal / 3
            Else
                SplitVal = -Range("B2").Value - 1
                Range("B1").Value = SplitVal / 3
                Range("B3").Value = SplitVal / 3
                Range("B4").Value = SplitVal / 3
            End If
        
        Else
        
            If Not (Intersect(target, Range("B3")) Is Nothing) Then
                Range("B1").NumberFormat = "0.00%"
                Range("B2").NumberFormat = "0.00%"
                Range("B3").NumberFormat = "0.00%"
                Range("B4").NumberFormat = "0.00%"
                If Range("B3").Value >= 0 Then
                    SplitVal = 1 - Range("B3").Value
                    Range("B1").Value = SplitVal / 3
                    Range("B2").Value = SplitVal / 3
                    Range("B4").Value = SplitVal / 3
                Else
                    SplitVal = -Range("B3").Value - 1
                    Range("B1").Value = SplitVal / 3
                    Range("B2").Value = SplitVal / 3
                    Range("B4").Value = SplitVal / 3
                End If
        
            Else
            
                If Not (Intersect(target, Range("B4")) Is Nothing) Then
                    Range("B1").NumberFormat = "0.00%"
                    Range("B2").NumberFormat = "0.00%"
                    Range("B3").NumberFormat = "0.00%"
                    Range("B4").NumberFormat = "0.00%"
                    If Range("B4").Value >= 0 Then
                        SplitVal = 1 - Range("B4").Value
                        Range("B1").Value = SplitVal / 3
                        Range("B2").Value = SplitVal / 3
                        Range("B3").Value = SplitVal / 3
                    Else
                        SplitVal = -Range("B4").Value - 1
                        Range("B1").Value = SplitVal / 3
                        Range("B2").Value = SplitVal / 3
                        Range("B3").Value = SplitVal / 3
                    End If
                End If
            End If
        End If
    End If
    
    Application.EnableEvents = True

End Sub

Open in new window

gowflow
percentage-V2.xls
0
gowflowCommented:
Any chance to have tried the proposed solution ?
gowflow
0
gixxer1020Author Commented:
Hi gowflow,

I've tried the code but it doesn't seem to work.
To answer your question here is an example of what I'm looking for.
In watching three cells, I would enter data in two cells and the third would automatically calculate so that all three cells combined total 100%.  However I would need to be able to enter data into any of the three cells.

Thanks,

Edwin
0
gowflowCommented:
ok pls give me what are the 3 cells A1,A2,A3 would be fine ???

Did you try the file I posted ??? percentage v2.xls ???? what do you mean it does not work ?? did you enable macros ?? it should work but maybe not what you want. Please clarify your statement so we can help you better. If you can reply fast we can solve your issue even faster.

Regards
gowflow
0
gowflowCommented:
ok I just noticed that  I maybe wrote something wrong in my previous file attached in ID: 39572938 I talked about The cells concerned are A1,A2,A3,A4 where infact I meant The cells concerned are B1,B2,B3,B4.

Anyway I just created an other file now that is attached where you just input in the colored cells B1, B2, B3 any value that you want and look at the rest interacting I have put in C3 a sum of all 3 cells that will give you the total at any time.

Pls check it try it and let me know.
gowflow
percentage-V3.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gixxer1020Author Commented:
Thanks goflow....that works perfectly

Edwin
0
gowflowCommented:
Happy we got to it at the end and glad I could help. Sorry for the confusion in between
gowflow
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.