Hello everyone.

When added, this code inputs changed values in a range ("g5:g29") to
specified ("g31") cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

When formula =IF(C6+E6=0;"";B6-C6+E6) was added to "g6", i hoped this result
would generate input in"g31", if input in "c6" or "e6" (due to code), but
only values from either"c6" or "e6" seem to be added.

What am I doing wrong?

Thanks in advance:)

You need a different Event Macro.

The Change event traps changes due to typeing or pasting. The Calculate
event responds to calculation changes.
--
Gary''s Student - gsnu200798


"New Ton" wrote:

Hello everyone.

When added, this code inputs changed values in a range ("g5:g29") to
specified ("g31") cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

When formula =IF(C6+E6=0;"";B6-C6+E6) was added to "g6", i hoped this result
would generate input in"g31", if input in "c6" or "e6" (due to code), but
only values from either"c6" or "e6" seem to be added.

What am I doing wrong?

Thanks in advance:)

Ok, thanks.
But what identifier etc. do i use? (I completely NOOB with this)

Thanks Gary''s Student

"Gary''s Student" wrote:

You need a different Event Macro.

The Change event traps changes due to typeing or pasting. The Calculate
event responds to calculation changes.
--
Gary''s Student - gsnu200798


"New Ton" wrote:

Hello everyone.

When added, this code inputs changed values in a range ("g5:g29") to
specified ("g31") cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

When formula =IF(C6+E6=0;"";B6-C6+E6) was added to "g6", i hoped this result
would generate input in"g31", if input in "c6" or "e6" (due to code), but
only values from either"c6" or "e6" seem to be added.

What am I doing wrong?

Thanks in advance:)

Don't now if I am asking the right questions here, but when i change code to
this;

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

I get this compile error: 'Procedure declaration does not match
description...'

Do i need to change code completely or is it something in the declaration
and in that case, what?

Thank you:)

"Gary''s Student" wrote:

You need a different Event Macro.

The Change event traps changes due to typeing or pasting. The Calculate
event responds to calculation changes.
--
Gary''s Student - gsnu200798


"New Ton" wrote:

Hello everyone.

When added, this code inputs changed values in a range ("g5:g29") to
specified ("g31") cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

When formula =IF(C6+E6=0;"";B6-C6+E6) was added to "g6", i hoped this result
would generate input in"g31", if input in "c6" or "e6" (due to code), but
only values from either"c6" or "e6" seem to be added.

What am I doing wrong?

Thanks in advance:)

Use those dropdowns at the top of the code window when you're in the worksheet
module.

You can choose Worksheet from the lefthand side dropdown and you can choose the
event that you want from the righthand side dropdown.

And you'll see this for the Calculate event:
Private Sub Worksheet_Calculate()

There is no target parm passed to this sub.

I'm not sure where what you're doing, but maybe...

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("g31").Value = Me.Range("g6").Value
Application.EnableEvents = True
End Sub

New Ton wrote:

Don't now if I am asking the right questions here, but when i change code to
this;

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

I get this compile error: 'Procedure declaration does not match
description...'

Do i need to change code completely or is it something in the declaration
and in that case, what?

Thank you:)

"Gary''s Student" wrote:

You need a different Event Macro.

The Change event traps changes due to typeing or pasting. The Calculate
event responds to calculation changes.
--
Gary''s Student - gsnu200798


"New Ton" wrote:

Hello everyone.

When added, this code inputs changed values in a range ("g5:g29") to
specified ("g31") cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

When formula =IF(C6+E6=0;"";B6-C6+E6) was added to "g6", i hoped this result
would generate input in"g31", if input in "c6" or "e6" (due to code), but
only values from either"c6" or "e6" seem to be added.

What am I doing wrong?

Thanks in advance:)


--

Dave Peterson

Ok, Dave thanks for that.

What I am trying to do is to register money in and out, sorted on several
accounts.
There is a total sheet and one sheet for each account.
On these account sheets there are columns for 'date'(A), 'total
dept'(lended)(B)), 'money paid'(C), 'money lended'(E) and 'new total dept'(G)


A is generated if input C or E = TRUE, thus creating action on specific date.
G is generated by calculating B-C+E

At the bottom of each account sheet, there is one cell (G31) that should
hold the last 'new total dept' entered.
I did this with the original CHANGE EVENT, but when I added formula for
input in G-column, the return in G31 was only the input of either B or C
cells.

Example: A1=080808, B1=$1000, C1=$100, E1=$10, G1=$910
A2=150808, B2=$910, C2=$10, E2=$200, G2=$1100
........etc.

G31=1100 (G2-This is what i want, not B2 or C2.
I hope my bad explanation makes any sense, thanx alot for all help so far:)

"Dave Peterson" wrote:

Use those dropdowns at the top of the code window when you're in the worksheet
module.

You can choose Worksheet from the lefthand side dropdown and you can choose the
event that you want from the righthand side dropdown.

And you'll see this for the Calculate event:
Private Sub Worksheet_Calculate()

There is no target parm passed to this sub.

I'm not sure where what you're doing, but maybe...

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("g31").Value = Me.Range("g6").Value
Application.EnableEvents = True
End Sub

New Ton wrote:

Don't now if I am asking the right questions here, but when i change code to
this;

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

I get this compile error: 'Procedure declaration does not match
description...'

Do i need to change code completely or is it something in the declaration
and in that case, what?

Thank you:)

"Gary''s Student" wrote:

You need a different Event Macro.

The Change event traps changes due to typeing or pasting. The Calculate
event responds to calculation changes.
--
Gary''s Student - gsnu200798


"New Ton" wrote:

Hello everyone.

When added, this code inputs changed values in a range ("g5:g29") to
specified ("g31") cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

When formula =IF(C6+E6=0;"";B6-C6+E6) was added to "g6", i hoped this result
would generate input in"g31", if input in "c6" or "e6" (due to code), but
only values from either"c6" or "e6" seem to be added.

What am I doing wrong?

Thanks in advance:)


--

Dave Peterson

Ok Dave, I tried your snippet of code, and it got me a lot closer.
It returns value calculated in "g6" only.
If i want it to return next value "g7", "g8", "g9" etc. when calculated,
what do i do?

Thanks a heap!

"New Ton" wrote:

Ok, Dave thanks for that.

What I am trying to do is to register money in and out, sorted on several
accounts.
There is a total sheet and one sheet for each account.
On these account sheets there are columns for 'date'(A), 'total
dept'(lended)(B)), 'money paid'(C), 'money lended'(E) and 'new total dept'(G)


A is generated if input C or E = TRUE, thus creating action on specific date.
G is generated by calculating B-C+E

At the bottom of each account sheet, there is one cell (G31) that should
hold the last 'new total dept' entered.
I did this with the original CHANGE EVENT, but when I added formula for
input in G-column, the return in G31 was only the input of either B or C
cells.

Example: A1=080808, B1=$1000, C1=$100, E1=$10, G1=$910
A2=150808, B2=$910, C2=$10, E2=$200, G2=$1100
........etc.

G31=1100 (G2-This is what i want, not B2 or C2.
I hope my bad explanation makes any sense, thanx alot for all help so far:)

"Dave Peterson" wrote:

Use those dropdowns at the top of the code window when you're in the worksheet
module.

You can choose Worksheet from the lefthand side dropdown and you can choose the
event that you want from the righthand side dropdown.

And you'll see this for the Calculate event:
Private Sub Worksheet_Calculate()

There is no target parm passed to this sub.

I'm not sure where what you're doing, but maybe...

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("g31").Value = Me.Range("g6").Value
Application.EnableEvents = True
End Sub

New Ton wrote:

Don't now if I am asking the right questions here, but when i change code to
this;

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

I get this compile error: 'Procedure declaration does not match
description...'

Do i need to change code completely or is it something in the declaration
and in that case, what?

Thank you:)

"Gary''s Student" wrote:

You need a different Event Macro.

The Change event traps changes due to typeing or pasting. The Calculate
event responds to calculation changes.
--
Gary''s Student - gsnu200798


"New Ton" wrote:

Hello everyone.

When added, this code inputs changed values in a range ("g5:g29") to
specified ("g31") cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("g5:g29"), Target) Is Nothing Then
Range("g31").Value = Target
End If
End Sub

When formula =IF(C6+E6=0;"";B6-C6+E6) was added to "g6", i hoped this result
would generate input in"g31", if input in "c6" or "e6" (due to code), but
only values from either"c6" or "e6" seem to be added.

What am I doing wrong?

Thanks in advance:)


--

Dave Peterson