Thank you GD this works great and I even figured out hoe to copy it over
multiple times with:

Const WS_RANGE As String = "B1:B30,d1:d30, etc "

Thanks everyone for your help

fryguy

"Gord Dibben" wrote:

Maybe this does what you want.

Makes each cell in C1:C30 an accumulator for cells in B1:B30

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B30"
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If Target.Value < "" Then
With Target.Offset(0, 1)
.Value = Target.Value + .Value
End With
End If
Next cell
End If

ws_exit:
Application.EnableEvents = True
End Sub

Or do you want just one cell............C3 to be an accumulator for B1:B30?

With Range("C3") instead of With Target.Offset(0, 1)


Gord Dibben MS Excel MVP


On Sat, 12 Jan 2008 12:56:01 -0800, fryguy
wrote:

Thank you GS this one looks great, and easier to understand, but how would I
be able to expand (I should have asked the first time) the range to include a
verticle range as the rb and rc values?

I tried changing the

Set rb = Range("B3") to Set rb = Range("B:B")
Set rc = Range("C3") to Set rc = Range("C:C")

and tried defining names for B3:B30 and same for C3:C30 and plugging the
names in but nothing. Would I have to make it an array!? Arrays mess with
my head.

Thanks for any help you can provide.

fryguy


"Gary''s Student" wrote:

Let's build an accumulator. From a clean sheet if we enter 5 in B3, then 5
will appear in C3. If we next enter 12 in B3, then 17 will appear in
C3...etc.


Private Sub Worksheet_Change(ByVal Target As Range)
Set rb = Range("B3")
Set rc = Range("C3")
If Intersect(Target, rb) Is Nothing Then Exit Sub
Application.EnableEvents = False
rc.Value = rc.Value + rb.Value
Application.EnableEvents = True
End Sub


--
Gary''s Student - gsnu200765


"fryguy" wrote:

how can i make an event macro to update the value in C3 when a new value is
entered in B3 without the need to click an update button.

thanks,

fryguy.