Help with code for two cell accumulator
 

I have the following code:

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

This is a two cell accumulator that works for ONE row. I
would like to modify it to work for ALL rows in a
worksheet. Any help would be appreciated.

Help with code for two cell accumulator
 

One way:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then
Exit Sub
End If
If .Column = 2 Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
Application.EnableEvents = True
End If
End If
End With

End Sub

ToddG wrote:

I have the following code:

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

This is a two cell accumulator that works for ONE row. I
would like to modify it to work for ALL rows in a
worksheet. Any help would be appreciated.

--

Dave Peterson

Help with code for two cell accumulator
 

That did it...Thanks much Dave
-----Original Message-----
One way:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then
Exit Sub
End If
If .Column = 2 Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value
+ .Value
Application.EnableEvents = True
End If
End If
End With

End Sub

ToddG wrote:

I have the following code:

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

This is a two cell accumulator that works for ONE row. I
would like to modify it to work for ALL rows in a
worksheet. Any help would be appreciated.

--

Dave Peterson

.

Help with code for two cell accumulator
 

"ToddG" wrote in message ...
I have the following code:

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

This is a two cell accumulator that works for ONE row. I
would like to modify it to work for ALL rows in a
worksheet. Any help would be appreciated.


Hi!
Maybe this is what you are looking for:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False

.Offset(, 1).Value = .Offset(, 1).Value + .Value

Application.EnableEvents = True
End If
End With
End Sub

I have changed your cell accumulator, so that the values of the target
cell are added up in the cell right to the target cell(row offet =1).

Michi