Help with code for two cell accumulator

  • Thread starter Thread starter ToddG
  • Start date Start date
T

ToddG

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.
 
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
 
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


--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
ToddG said:
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.
Click to expand...


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
 
Back
Top