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" <Stilltrader47@discussions.microsoft.com> skrev i 
meddelelsen news:CBA2CCFB-008F-4985-8C8F-175ECAE00EE4@microsoft.com...
> 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