Entering Values in a Single Cell to Update Next Empty Cell in a Ra

[Stilltrader47]

I would appreciate help with a macro or function to accomplish the following:

I would like to enter values in a single "data entry" cell and have it
update
the next consecutive empty cell in a cell range. For example, cell j29 is the
data entry cell:

Each time I enter a value in j29, the next empty cell in range b139 through
b150 will update. And each value that populates in b139:b150 should be
frozen to a "value". I am thinking I have to include a
"edit->copy->edit->paste special-> values" macro to freeze every entry after
it places.

Every time I enter a value in j29 the below range should update. Your input
on how
to do this will be appreciated.

B
139 55
140 84
141 73
142 12
143 22
144
145
146
147
148
149
150

 

[Ryan H]

What are you wanting to put in range B139:B150? Are you wanting to put the
value you entered in J29 in the next availabe cell in B139:B150? If so, what
happens when B139:B150 gets full?

 

[Stilltrader47]

Thanks Ryan:

For example, I'll enter 55 in j29, and it posts to b139. Then when I enter
84 in j29, it posts to b140, and so on. I will eventually purge the values
entered in b139:b150, some time after the value for b150 is entered and
posted.

Thanks for reviewing and your advicce - Tom

 

[Per Jessen]

Hi Tom,

Insert this event code in the codesheet for your input sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DestCell As Range
If Target.Address = "$J$29" Then
Application.EnableEvents = False
If Range("B139").Value = "" Then
Range("B139") = Range("J29").Value
Else
Set DestCell = Range("B151").End(xlUp).Offset(1)
If DestCell.Row <= 150 Then
DestCell = Target.Value
Else
msg = MsgBox("Range B139:B150 is full!", vbExclamation +
vbOKOnly)
End If
End If
Application.EnableEvents = True
End If
End Sub

Regards,
Per