Updatig the Next Consecutive Empty Cell in a Range

  • Thread starter Thread starter Stilltrader47
  • Start date Start date
S

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