Thread: 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

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" <Chris@discussions.microsoft.com> wrote in message
news:796C5E5B-0D26-446F-BE6E-7C076D842BA4@microsoft.com...
> 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

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 <796C5E5B-0D26-446F-BE6E-7C076D842BA4@microsoft.com>,
"Chris" <Chris@discussions.microsoft.com> 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

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" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-1531DF.16393626082005@msnews.microsoft.com...
> 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 <796C5E5B-0D26-446F-BE6E-7C076D842BA4@microsoft.com>,
> "Chris" <Chris@discussions.microsoft.com> 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

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 <430fc27d_2@newspeer2.tds.net>,
"Patti" <anonymous@discussions.microsoft.com> 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!

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 <796C5E5B-0D26-446F-BE6E-7C076D842BA4@microsoft.com>,
> "Chris" <Chris@discussions.microsoft.com> 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
>

Hi,

I am trying to solve a similar issue but am pretty inexperienced with computer programming other than some basic c++ classes.

I am looking to create a circular reference between a group of cells on two different sheets. If I type something in one it will carry to the other sheet. So I can follow along with the code could you provide me with the code that will link cell C5 (sheet 1) to cell D22 (sheet 2), C6 (sheet 1) to cell D23 (sheet 2), C7 (sheet 1) to cell D24 (sheet 2). Sorry I'm a nube with this.

Thanks!

N