Please help fix my accumulator
 

I've read everything in this group about accumulators, and have
cobbled together some code. I'm puzzled why it won't work. I'm just
looking for a summary worksheet to capture daily journal entries
into a monthly entry, like so:

ClosingEntry SummaryClosingEntry

Cash Cash
CreditCard1 CreditCard1
CreditCard2 CreditCard2
Etc. Etc.

One workbook, two worksheets, code is in the ClosingEntry worksheet
module. Thanks for your help, in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address(False, False, , False) =
Worksheets("ClosingEntry").Range("C5") Then
If IsNumeric(.Value) Then
Application.EnableEvents = False

Worksheets("SummaryClosingEntry").Range("C5").Valu e = _
Worksheets("ClosingEntry").Range("C5").Value +
..Value
Application.EnableEvents = True
End If
End If
End With

End Sub

Please help fix my accumulator
 

One reason is that you're comparing a string (.Address) to a cell value
(.Range("C5")).

I can't tell from your example whether you're trying to implement a
one-cell accumulator or a two-cell accumulator. You only have one cell
reference (C5), but you're not using a static or global variable to
retain the initial value, as is required with a single-cell accumulator.

You might take a look at

http://www.mcgimpsey.com/excel/accumulator.html


Note that you don't have to qualify the ranges when your code is in the
same worksheet as the reference, i.e., in the ClosingEntry worksheet
module,

Worksheets("ClosingEntry").Range("C5")

and

Range("C5")

are equivalent to

Me.Range("C5")

(it doesn't hurt anything, though).

In article ,
Tim McMahon wrote:

I've read everything in this group about accumulators, and have
cobbled together some code. I'm puzzled why it won't work. I'm just
looking for a summary worksheet to capture daily journal entries
into a monthly entry, like so:

ClosingEntry SummaryClosingEntry

Cash Cash
CreditCard1 CreditCard1
CreditCard2 CreditCard2
Etc. Etc.

One workbook, two worksheets, code is in the ClosingEntry worksheet
module. Thanks for your help, in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address(False, False, , False) =
Worksheets("ClosingEntry").Range("C5") Then
If IsNumeric(.Value) Then
Application.EnableEvents = False

Worksheets("SummaryClosingEntry").Range("C5").Valu e = _
Worksheets("ClosingEntry").Range("C5").Value +
.Value
Application.EnableEvents = True
End If
End If
End With

End Sub

Please help fix my accumulator
 

Tim,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim shSummary As Worksheet

Set shSummary = Worksheets("SummaryClosingEntry")
With Target
If .Address(False, False, , False) = "C5" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
shSummary.Range("C5").Value = _
shSummary.Range("C5").Value + .Value
Application.EnableEvents = True
End If
End If
End With

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Tim McMahon" wrote in message
...
I've read everything in this group about accumulators, and have
cobbled together some code. I'm puzzled why it won't work. I'm just
looking for a summary worksheet to capture daily journal entries
into a monthly entry, like so:

ClosingEntry SummaryClosingEntry

Cash Cash
CreditCard1 CreditCard1
CreditCard2 CreditCard2
Etc. Etc.

One workbook, two worksheets, code is in the ClosingEntry worksheet
module. Thanks for your help, in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address(False, False, , False) =
Worksheets("ClosingEntry").Range("C5") Then
If IsNumeric(.Value) Then
Application.EnableEvents = False

Worksheets("SummaryClosingEntry").Range("C5").Valu e = _
Worksheets("ClosingEntry").Range("C5").Value +
.Value
Application.EnableEvents = True
End If
End If
End With

End Sub

Please help fix my accumulator
 

Oops, I missed your call to SummaryClosingEntry. Here's one way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "C5" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
With Sheets("SummaryClosingEntry").Range(.Address)
.Value = .Value + Target.Value
End With
Application.EnableEvents = True
End If
End If
End With
End Sub




In article ,
JE McGimpsey wrote:

One reason is that you're comparing a string (.Address) to a cell value
(.Range("C5")).

I can't tell from your example whether you're trying to implement a
one-cell accumulator or a two-cell accumulator. You only have one cell
reference (C5), but you're not using a static or global variable to
retain the initial value, as is required with a single-cell accumulator.

You might take a look at

http://www.mcgimpsey.com/excel/accumulator.html


Note that you don't have to qualify the ranges when your code is in the
same worksheet as the reference, i.e., in the ClosingEntry worksheet
module,

Worksheets("ClosingEntry").Range("C5")

and

Range("C5")

are equivalent to

Me.Range("C5")

(it doesn't hurt anything, though).

In article ,
Tim McMahon wrote:

I've read everything in this group about accumulators, and have
cobbled together some code. I'm puzzled why it won't work. I'm just
looking for a summary worksheet to capture daily journal entries
into a monthly entry, like so:

ClosingEntry SummaryClosingEntry

Cash Cash
CreditCard1 CreditCard1
CreditCard2 CreditCard2
Etc. Etc.

One workbook, two worksheets, code is in the ClosingEntry worksheet
module. Thanks for your help, in advance.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address(False, False, , False) =
Worksheets("ClosingEntry").Range("C5") Then
If IsNumeric(.Value) Then
Application.EnableEvents = False

Worksheets("SummaryClosingEntry").Range("C5").Valu e = _
Worksheets("ClosingEntry").Range("C5").Value +
.Value
Application.EnableEvents = True
End If
End If
End With

End Sub