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

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?

--
Cheers,
Ryan


"Stilltrader47" wrote:

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

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

"Ryan H" wrote:

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?

--
Cheers,
Ryan


"Stilltrader47" wrote:

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

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

"Stilltrader47" skrev i
meddelelsen ...
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

"Ryan H" wrote:

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?

--
Cheers,
Ryan


"Stilltrader47" wrote:

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