Entering Values in a Single Cell to Update Next Empty Cell in
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
|