using VBA to get away from circular reference
 

Hi

I want to use VBA to do the following.

4 columns sum to equal the 5 column

entering a number into one of the 4 columns retotals the 5 column

changing the 5th column divides the new number by 4 and puts this value
into the each of the first 4 columns.

put another way:

Q1 + Q2 + Q3 +Q4 = Whole Year when columns 1 through 4 are edited

whole year/4 whole year/4 whole year/4 whole year/4 when column whole
year is edited.

I suspect i'll have to do event capturing of cell clicks, move in, move out,
up, down
and find where I am in the spreadsheet for the columns relative to the whole
year column and vice versa

is this feasible or is there another way around this?

thanks

Chris

using VBA to get away from circular reference
 

Chris,

When you change one of the first four cells and it then changes the Whole
Year cell, do you then want the first 4 cells to then change to equal WY/4.
Or does WY/4 only happen when the user directly changes WY?

In either case I believe you'll have to use Worksheet Change events. A
helper column that contains the value (not a formula) of the current value
in WY might simplify things.

Doug

"Chris" wrote in message
...
Hi

I want to use VBA to do the following.

4 columns sum to equal the 5 column

entering a number into one of the 4 columns retotals the 5 column

changing the 5th column divides the new number by 4 and puts this value
into the each of the first 4 columns.

put another way:

Q1 + Q2 + Q3 +Q4 = Whole Year when columns 1 through 4 are edited

whole year/4 whole year/4 whole year/4 whole year/4 when column whole
year is edited.

I suspect i'll have to do event capturing of cell clicks, move in, move
out,
up, down
and find where I am in the spreadsheet for the columns relative to the
whole
year column and vice versa

is this feasible or is there another way around this?

thanks

Chris

using VBA to get away from circular reference
 

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
With Cells(.Row, 1).Resize(1, 5)
If Not Intersect(Target, .Cells) Is Nothing Then
Application.EnableEvents = False
If Target.Column = .Offset(1, 4).Column Then
.Resize(1, 4).Value = Target.Value / 4
Else
.Offset(0, 4).Resize(1, 1).Value = _
Application.Sum(.Resize(1, 4))
End If
Application.EnableEvents = True
End If
End With
End With
End Sub

Change the column in Cells(.Row, 1) to suit.


In article ,
"Chris" wrote:

Hi

I want to use VBA to do the following.

4 columns sum to equal the 5 column

entering a number into one of the 4 columns retotals the 5 column

changing the 5th column divides the new number by 4 and puts this value
into the each of the first 4 columns.

put another way:

Q1 + Q2 + Q3 +Q4 = Whole Year when columns 1 through 4 are edited

whole year/4 whole year/4 whole year/4 whole year/4 when column whole
year is edited.

I suspect i'll have to do event capturing of cell clicks, move in, move out,
up, down
and find where I am in the spreadsheet for the columns relative to the whole
year column and vice versa

is this feasible or is there another way around this?

thanks

Chris

using VBA to get away from circular reference
 

This is very cool JE... I'm using this as a learning tool, and I'm just
wondering if there is any reason that you used:

If Target.Column = .Offset(1, 4).Column Then
rather than:
If Target.Column = .Offset(0, 4).Column Then

I'm guessing that since you are only interested in the column at this point,
it doesn't really matter what you offset the rows by - but I've guessed
wrong before!

Thanks,

Patti



"JE McGimpsey" wrote in message
...
One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
With Cells(.Row, 1).Resize(1, 5)
If Not Intersect(Target, .Cells) Is Nothing Then
Application.EnableEvents = False
If Target.Column = .Offset(1, 4).Column Then
.Resize(1, 4).Value = Target.Value / 4
Else
.Offset(0, 4).Resize(1, 1).Value = _
Application.Sum(.Resize(1, 4))
End If
Application.EnableEvents = True
End If
End With
End With
End Sub

Change the column in Cells(.Row, 1) to suit.


In article ,
"Chris" wrote:

Hi

I want to use VBA to do the following.

4 columns sum to equal the 5 column

entering a number into one of the 4 columns retotals the 5 column

changing the 5th column divides the new number by 4 and puts this value
into the each of the first 4 columns.

put another way:

Q1 + Q2 + Q3 +Q4 = Whole Year when columns 1 through 4 are edited

whole year/4 whole year/4 whole year/4 whole year/4 when column
whole
year is edited.

I suspect i'll have to do event capturing of cell clicks, move in, move
out,
up, down
and find where I am in the spreadsheet for the columns relative to the
whole
year column and vice versa

is this feasible or is there another way around this?

thanks

Chris

using VBA to get away from circular reference
 

It's actually just an artifact of a different method I tried first. But
you're right, since it didn't seem to matter, I didn't bother changing
it.

Actually, it could matter - it will cause the routine to fail if a value
is entered in E65536. So the 1 should be changed to 0.

Thanks for the correction!

In article ,
"Patti" wrote:

I'm guessing that since you are only interested in the column at this point,
it doesn't really matter what you offset the rows by - but I've guessed
wrong before!

using VBA to get away from circular reference
 

Hi,

thanks for the reply. it works great as a foundation. I am doing a proof of
concept.

the code you gracially provided worked when you click left or right of the
last edited cell, however it does not function on the first immediate click
to a cell above or beneath the lasted edited cell.

I've tried editing your code but was unsuccessful.
Could you providet the changes to the cells are updated on a change to the
top or bottom.

thanks

Chris

"JE McGimpsey" wrote:

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
With Cells(.Row, 1).Resize(1, 5)
If Not Intersect(Target, .Cells) Is Nothing Then
Application.EnableEvents = False
If Target.Column = .Offset(1, 4).Column Then
.Resize(1, 4).Value = Target.Value / 4
Else
.Offset(0, 4).Resize(1, 1).Value = _
Application.Sum(.Resize(1, 4))
End If
Application.EnableEvents = True
End If
End With
End With
End Sub

Change the column in Cells(.Row, 1) to suit.


In article ,
"Chris" wrote:

Hi

I want to use VBA to do the following.

4 columns sum to equal the 5 column

entering a number into one of the 4 columns retotals the 5 column

changing the 5th column divides the new number by 4 and puts this value
into the each of the first 4 columns.

put another way:

Q1 + Q2 + Q3 +Q4 = Whole Year when columns 1 through 4 are edited

whole year/4 whole year/4 whole year/4 whole year/4 when column whole
year is edited.

I suspect i'll have to do event capturing of cell clicks, move in, move out,
up, down
and find where I am in the spreadsheet for the columns relative to the whole
year column and vice versa

is this feasible or is there another way around this?

thanks

Chris