I am tracking a bank account balance and would like that whenever a new value
is entered in cell c3, it updates the next consecutive empty cell in range
c5:c200. Please advise how I can write the formula, function or macro.

Thanks Sony

You need event code to do what you want. Try this... right click the tab at
the bottom of the worksheet where you want this functionality, select "View
Code" from the popup menu that appears and copy/paste the following into the
code window that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Target.Address(0, 0) = "C3" Then
LastRow = Range("C200").End(xlUp).Row
If LastRow = 5 Then Exit Sub
If LastRow = 3 Then LastRow = 5
If LastRow < 200 Then Cells(LastRow + 1, "C").Value = Range("C3")
End If
End Sub

That's it. Now go back to the worksheet and type something into C3... it
will be placed into the first empty cell in the range C5:C200.

--
Rick (MVP - Excel)


I am tracking a bank account balance and would like that whenever a new
value
is entered in cell c3, it updates the next consecutive empty cell in range
c5:c200. Please advise how I can write the formula, function or macro.

Thanks Sony

Here is different way to do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "C3" Then
On Error Resume Next
Range("C5:C200").SpecialCells(xlCellTypeBlanks)(1) = Range("C3")
If Err.Number And Range("C5") = "" Then Range("C5") = Range("C3")
End If
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
You need event code to do what you want. Try this... right click the tab
at the bottom of the worksheet where you want this functionality, select
"View Code" from the popup menu that appears and copy/paste the following
into the code window that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Target.Address(0, 0) = "C3" Then
LastRow = Range("C200").End(xlUp).Row
If LastRow = 5 Then Exit Sub
If LastRow = 3 Then LastRow = 5
If LastRow < 200 Then Cells(LastRow + 1, "C").Value = Range("C3")
End If
End Sub

That's it. Now go back to the worksheet and type something into C3... it
will be placed into the first empty cell in the range C5:C200.

--
Rick (MVP - Excel)


I am tracking a bank account balance and would like that whenever a new
value
is entered in cell c3, it updates the next consecutive empty cell in
range
c5:c200. Please advise how I can write the formula, function or macro.

Thanks Sony

Rick, Thank you. I will apply this and then reply. Much appreciated - Tom


"Rick Rothstein" wrote:

Here is different way to do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "C3" Then
On Error Resume Next
Range("C5:C200").SpecialCells(xlCellTypeBlanks)(1) = Range("C3")
If Err.Number And Range("C5") = "" Then Range("C5") = Range("C3")
End If
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
You need event code to do what you want. Try this... right click the tab
at the bottom of the worksheet where you want this functionality, select
"View Code" from the popup menu that appears and copy/paste the following
into the code window that appears...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Long
If Target.Address(0, 0) = "C3" Then
LastRow = Range("C200").End(xlUp).Row
If LastRow = 5 Then Exit Sub
If LastRow = 3 Then LastRow = 5
If LastRow < 200 Then Cells(LastRow + 1, "C").Value = Range("C3")
End If
End Sub

That's it. Now go back to the worksheet and type something into C3... it
will be placed into the first empty cell in the range C5:C200.

--
Rick (MVP - Excel)


I am tracking a bank account balance and would like that whenever a new
value
is entered in cell c3, it updates the next consecutive empty cell in
range
c5:c200. Please advise how I can write the formula, function or macro.

Thanks Sony

.