Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
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:) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
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:) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
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:) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
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:) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
I still don't understand where things come from or where they go, but maybe...
Option Explicit Private Sub Worksheet_Calculate() Application.EnableEvents = False Me.Range("g31").Value = Me.Range("g6").Value Me.Range("g32").Value = Me.Range("g7").Value Me.Range("g33").Value = Me.Range("g8").Value Me.Range("g34").Value = Me.Range("g9").Value Application.EnableEvents = True End Sub New Ton wrote: 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 -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
Thanks again for helping me out, guys like you save us newbies alot of
trouble :) So far i got calculation for (g6) returned in (g31), like you showed me. Next, when (g7) is calculated (this happens by input in other cells on row 7), i want that result to replace the previous value in (g31) that came from (g6). (In reality, last data in range) Would this work? Option Explicit Private Sub Worksheet_Calculate() Application.EnableEvents = False Me.Range("g31").Value = Me.Range("g6").Value Me.Range("g31").Value = Me.Range("g7").Value Me.Range("g31").Value = Me.Range("g8").Value Me.Range("g31").Value = Me.Range("g9").Value Application.EnableEvents = True End Sub "Dave Peterson" wrote: I still don't understand where things come from or where they go, but maybe... Option Explicit Private Sub Worksheet_Calculate() Application.EnableEvents = False Me.Range("g31").Value = Me.Range("g6").Value Me.Range("g32").Value = Me.Range("g7").Value Me.Range("g33").Value = Me.Range("g8").Value Me.Range("g34").Value = Me.Range("g9").Value Application.EnableEvents = True End Sub New Ton wrote: 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
The code would work fine--it just wouldn't do what you want.
I think you'll have to keep track of what cell whose value was last moved into G31. This is possible, but I'm not sure it'll help. This event fires each time excel recalculates this worksheet--no matter what caused the recalculation. Maybe looking at the last value that isn't equal to that empty string "" and moving that value to G31??? Option Explicit Private Sub Worksheet_Calculate() dim myRng as range dim myCell as range set myrng = me.range("g9,g8,g7,g6") for each mycell in myrng.cells if mycell.value = "" then 'do nothing else Application.EnableEvents = False Me.Range("g31").Value = myCell.value Application.EnableEvents = True exit for end if next mycell End Sub But this doesn't look like a good solution to me. New Ton wrote: Thanks again for helping me out, guys like you save us newbies alot of trouble :) So far i got calculation for (g6) returned in (g31), like you showed me. Next, when (g7) is calculated (this happens by input in other cells on row 7), i want that result to replace the previous value in (g31) that came from (g6). (In reality, last data in range) Would this work? Option Explicit Private Sub Worksheet_Calculate() Application.EnableEvents = False Me.Range("g31").Value = Me.Range("g6").Value Me.Range("g31").Value = Me.Range("g7").Value Me.Range("g31").Value = Me.Range("g8").Value Me.Range("g31").Value = Me.Range("g9").Value Application.EnableEvents = True End Sub "Dave Peterson" wrote: I still don't understand where things come from or where they go, but maybe... Option Explicit Private Sub Worksheet_Calculate() Application.EnableEvents = False Me.Range("g31").Value = Me.Range("g6").Value Me.Range("g32").Value = Me.Range("g7").Value Me.Range("g33").Value = Me.Range("g8").Value Me.Range("g34").Value = Me.Range("g9").Value Application.EnableEvents = True End Sub New Ton wrote: 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 -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Challenge with target output
Thank you Dave, that did the trick!:)
Dave Peterson skrev: The code would work fine--it just wouldn't do what you want. I think you'll have to keep track of what cell whose value was last moved into G31. This is possible, but I'm not sure it'll help. This event fires each time excel recalculates this worksheet--no matter what caused the recalculation. Maybe looking at the last value that isn't equal to that empty string "" and moving that value to G31??? Option Explicit Private Sub Worksheet_Calculate() dim myRng as range dim myCell as range set myrng = me.range("g9,g8,g7,g6") for each mycell in myrng.cells if mycell.value = "" then 'do nothing else Application.EnableEvents = False Me.Range("g31").Value = myCell.value Application.EnableEvents = True exit for end if next mycell End Sub But this doesn't look like a good solution to me. New Ton wrote: Thanks again for helping me out, guys like you save us newbies alot of trouble :) So far i got calculation for (g6) returned in (g31), like you showed me. Next, when (g7) is calculated (this happens by input in other cells on row 7), i want that result to replace the previous value in (g31) that came from (g6). (In reality, last data in range) Would this work? Option Explicit Private Sub Worksheet_Calculate() Application.EnableEvents = False Me.Range("g31").Value = Me.Range("g6").Value Me.Range("g31").Value = Me.Range("g7").Value Me.Range("g31").Value = Me.Range("g8").Value Me.Range("g31").Value = Me.Range("g9").Value Application.EnableEvents = True End Sub "Dave Peterson" wrote: I still don't understand where things come from or where they go, but maybe... Option Explicit Private Sub Worksheet_Calculate() Application.EnableEvents = False Me.Range("g31").Value = Me.Range("g6").Value Me.Range("g32").Value = Me.Range("g7").Value Me.Range("g33").Value = Me.Range("g8").Value Me.Range("g34").Value = Me.Range("g9").Value Application.EnableEvents = True End Sub New Ton wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge | Excel Worksheet Functions | |||
Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
A Challenge | Excel Discussion (Misc queries) | |||
A Challenge | Excel Worksheet Functions | |||
Who is up for a challenge? | Excel Discussion (Misc queries) |