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

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

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
.