A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Entering Values and Updating Next Empty Cell in a Range



 
 
Thread Tools Display Modes
  #1  
Old March 6th, 2010, 06:34 AM posted to microsoft.public.excel.worksheet.functions
Stilltrader47
external usenet poster
 
Posts: 19
Default 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  
Old March 6th, 2010, 11:24 AM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default 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  
Old March 14th, 2010, 04:53 AM posted to microsoft.public.excel.worksheet.functions
Stilltrader47
external usenet poster
 
Posts: 19
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:59 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.