I thought it would be simple

[stevehard]

I want to have a cell continually add the numerical values entered into another cell, so 55+40 entered into A2 would equal 95 in b2. I want to do this for the whole of column A but have the corresponding B column add eg. A5 is added in B5, A500 is added in B500. The following code adds the numbers entered in column A into cell B2, what do i change, obviously the B2 remark but to what?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If Target.Row > 1 And Target.Column = 1 Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B2").Value = Range("B2").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

Many Thanks

 

[Jonmo1]

Welcome to the board...
Use the Target.Row Property to determine the Row in Column B...

Try changing
Range("B2").Value = Range("B2").Value + .Value

to

Range("B" & .Row).Value = Range("B" & .Row).Value + .Value

Hope this helps.

 

[Jonmo1]

Also, this should make it a little more robust, as it is, it will error if more than 1 cell is changed at one time...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set MyRange = Intersect(Target, Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))
If Not MyRange Is Nothing Then
    Application.EnableEvents = False
    For Each C In MyRange
        If IsNumeric(C.Value) Then
            Range("B" & C.Row).Value = Range("B" & C.Row).Value + C.Value
        End If
    Next C
End If
Application.EnableEvents = True
End Sub

 

[stevehard]

Absolutely fantastic, only had time to try the first one shall try after work but I can't thank you enough.