Updatig the Next Consecutive Empty Cell in a Range

[Stilltrader47]

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 Rothstein]

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 Rothstein]

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

 

[Stilltrader47]

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