ExcelBanter - Always have cells add up to 100

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Always have cells add up to 100 (https://www.excelbanter.com/excel-programming/381293-always-have-cells-add-up-100-a.html)

Jeff[_53_]

Always have cells add up to 100

I have range of cells that are the same right now, and they add up to
100:

7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857

100

I would like to be able to change on of those values, and the others
adjust to still add up to 100. Any ideas?

Don Guillett

Always have cells add up to 100

Right click sheet tabview codeinsert thisSAVE workbook

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrng = Range("e2:e15")
Application.EnableEvents = False
If Not Intersect(Target, myrng) Is Nothing Then
x = 100 - Target
For Each c In myrng
If c.Address < Target.Address Then c.Value = x / 13
Next
End If
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software

"Jeff" wrote in message
oups.com...
I have range of cells that are the same right now, and they add up to
100:

7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857

100

I would like to be able to change on of those values, and the others
adjust to still add up to 100. Any ideas?

Don Guillett

Always have cells add up to 100

OP did not ask for it.
--
Don Guillett
SalesAid Software

"Charles Chickering" wrote in
message ...
Don, why not use:

f c.Address < Target.Address Then c.Value = x / myrng.Cells.Count

as opposed to a hard coded number?
--
Charles Chickering

"A good example is twice the value of good advice."

"Don Guillett" wrote:

Right click sheet tabview codeinsert thisSAVE workbook

Private Sub Worksheet_Change(ByVal Target As Range)
Set myrng = Range("e2:e15")
Application.EnableEvents = False
If Not Intersect(Target, myrng) Is Nothing Then
x = 100 - Target
For Each c In myrng
If c.Address < Target.Address Then c.Value = x / 13
Next
End If
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software

"Jeff" wrote in message
oups.com...
I have range of cells that are the same right now, and they add up to
100:

7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857
7.142857

100

I would like to be able to change on of those values, and the others
adjust to still add up to 100. Any ideas?

All times are GMT +1. The time now is 08:50 PM.