If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Entering Values and Updating Next Empty Cell in a Range
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 |
#2
|
|||
|
|||
Entering Values and Updating Next Empty Cell in a Range
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 tabView 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 |
#3
|
|||
|
|||
Entering Values and Updating Next Empty Cell in a Range
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 tabView 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 . |
Thread Tools | |
Display Modes | |
|
|