running totals

[MauraLae]

I have this VBA macro, that I think Dave Peterson wrote.....

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$5" Then
Application.EnableEvents = False
Range("E5").Value = Range("E5").Value + Target.Value
Application.EnableEvents = True
End If

End Sub

If I enter a number in B5 then the running total is in E5

I would like to adapt the Sub so that each number entered in B5 is
listed, starting in A1(or other column), like the tape on an adding
machine is visable.

Any suggestions?

 

[Guest]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$5" Then
Application.EnableEvents = False
Range("E5").Value = Range("E5").Value + Target.Value
j = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Cells(j, 1) = Target.Value
Application.EnableEvents = True
End If

End Sub

If you had lots of entries, you should also dim j as long

 

[Guest]

Sorry, needs a +1:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$5" Then
Application.EnableEvents = False
Range("E5").Value = Range("E5").Value + Target.Value
j = Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Cells(j, 1) = Target.Value
Application.EnableEvents = True
End If

End Sub

 

[Sandy Mann]

MauraLae,

Try adding code to give you:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$5" Then
Endrow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Application.EnableEvents = False
Cells(Endrow, 1).Value = Target.Value
Range("E5").Value = Range("E5").Value + Target.Value
Application.EnableEvents = True
End If

End Sub


--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[MauraLae]

Worked like a charm. Thanks much!