Entering Values and Updating Next Empty Cell in a Range
in [Functions]
|
Prev: How do I tell Excel to insert data from a cell 4 to the left?
Next: Finding Average of last 5 entries
From: Stilltrader47 on 6 Mar 2010 00:34 Please help, I hope there is a macro. Every time I enter a value in cell j29, I would like it to post to the next empty cell in range b139:b150. For example, if I enter 24 and it populates b139, the next value I enter in j29 should post in b140 Thanks - Tom
From: Roger Govier on 6 Mar 2010 05:24 Hi You don't say what you want to do if row 150 is already filled. This code will do what you want, and i have stopped it at B150. Remove the stop if that is what you want Private Sub Worksheet_Change(ByVal Target As Range) Dim lr As Long If Target.Address <> Range("J29").Address Then Exit Sub lr = Cells(Rows.Count, "B").End(xlUp).Row + 1 lr = Application.Max(139, lr) If lr > 150 Then MsgBox "Cell B150 already populated" Exit Sub End If Exit Sub Application.EnableEvents = False Cells(lr, "B") = Target.Value Application.EnableEvents = True End Sub Copy code above Right click on sheet tab>View code Post code into white pane that appears Alt+F11 to return to Excel -- Regards Roger Govier Stilltrader47 wrote: > Please help, I hope there is a macro. > > Every time I enter a value in cell j29, I would like it to post to the next > empty cell in range b139:b150. For example, if I enter 24 and it populates > b139, the next value I enter in j29 should post in b140 > > Thanks - Tom
From: Stilltrader47 on 13 Mar 2010 22:53
Roger - I apologize for the delay in getting back to you. I just had a chance tonight to try the code. When I entered the 1st value in cell j29, the message "Cell B150 already populated" displayed. However, cells b139:b150 are all empty. The value entered in j29 did not post/populate b139 (1st cell in range) as expected. I cannot see where the value updated anywhere. Please review and advise update. To reiterate, new values will be entered intermittently in cell j29. As each new value is intered, the objective is to update it to the next empty cell in range b139:b150. Thanks for your help - Tom "Roger Govier" wrote: > Hi > You don't say what you want to do if row 150 is already filled. > This code will do what you want, and i have stopped it at B150. > Remove the stop if that is what you want > > Private Sub Worksheet_Change(ByVal Target As Range) > Dim lr As Long > If Target.Address <> Range("J29").Address Then Exit Sub > > lr = Cells(Rows.Count, "B").End(xlUp).Row + 1 > lr = Application.Max(139, lr) > > > If lr > 150 Then > MsgBox "Cell B150 already populated" > Exit Sub > End If > > Exit Sub > Application.EnableEvents = False > Cells(lr, "B") = Target.Value > Application.EnableEvents = True > > End Sub > > Copy code above > Right click on sheet tab>View code > Post code into white pane that appears > Alt+F11 to return to Excel > > -- > Regards > Roger Govier > > Stilltrader47 wrote: > > Please help, I hope there is a macro. > > > > Every time I enter a value in cell j29, I would like it to post to the next > > empty cell in range b139:b150. For example, if I enter 24 and it populates > > b139, the next value I enter in j29 should post in b140 > > > > Thanks - Tom > . > |