Entering a Value & Updating the Next Empty Cell in a Range
in [Functions]
|
Prev: marvendas@gmail.com Kit completo de Solenoides ( solenoid ) + chicotePara Cambio automatico 01M hidramatico Audi A3 Vw Golf gti turbo 79245
Next: Sumproduct formula
From: Stilltrader47 on 19 Feb 2010 23:55 I would like to enter values in a single cell and have it update each time the next consecutive empty cell in a cell range. For example, cell c5 is the data entry cell: Each time I enter a value the next empty cell in range a10 through a20 is updated. And the previous entered is frozen to values. 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 c5, the below range update. Your input on how to do this will be appreciated. A 10 55 11 84 12 73 13 12 14 22 15 16 17 18 19 20
From: tompl on 20 Feb 2010 10:38 Copy this code to the macro area of the specific worksheet that you are working on and any value entered in cell C5 of that worksheet will be copied to the bottom of the column that starts with cell A10. Private Sub worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("C5")) Is Nothing Then rwRow = Range("A10").CurrentRegion.Rows _ (UBound(Range("A10").CurrentRegion.Value, 1)).Row Range("A" & rwRow + 1).Value = Range("C5").Value End If End Sub Let me know if you have further questions. Tom
From: Stilltrader47 on 20 Feb 2010 15:50 Thanks Tom, I will update you, and thanks for being open to followup questions - Sony "tompl" wrote: > Copy this code to the macro area of the specific worksheet that you are > working on and any value entered in cell C5 of that worksheet will be copied > to the bottom of the column that starts with cell A10. > > Private Sub worksheet_Change(ByVal Target As Range) > > If Not Intersect(Target, Range("C5")) Is Nothing Then > rwRow = Range("A10").CurrentRegion.Rows _ > (UBound(Range("A10").CurrentRegion.Value, 1)).Row > Range("A" & rwRow + 1).Value = Range("C5").Value > End If > > End Sub > > Let me know if you have further questions. > > Tom >
From: Stilltrader47 on 21 Feb 2010 01:36 Tom, Where in the worksheet do I put this code? And get it there. A separate tab? When I do this does it mean the entry will advance to the next empty cell? Thanks - Sony "tompl" wrote: > Copy this code to the macro area of the specific worksheet that you are > working on and any value entered in cell C5 of that worksheet will be copied > to the bottom of the column that starts with cell A10. > > Private Sub worksheet_Change(ByVal Target As Range) > > If Not Intersect(Target, Range("C5")) Is Nothing Then > rwRow = Range("A10").CurrentRegion.Rows _ > (UBound(Range("A10").CurrentRegion.Value, 1)).Row > Range("A" & rwRow + 1).Value = Range("C5").Value > End If > > End Sub > > Let me know if you have further questions. > > Tom >
From: tompl on 21 Feb 2010 10:53
This is a Macro and if you have not worked with them before it might be a little advanced for you. I am sorry if all this just adds confusion. To install this macro you need to open the VB Editor aka Macro Editor. First, open your workbook, then open the VB Editor by pressing Alt F11. With that open you should be able to see a list of worksheets in you workbook. If not press Ctrl R to open the Project Explorer. Next, double click on the worksheet name and the “Code” window will open and focused on that specific worksheet. Paste the code below into the “Code” window and that should do it. I re-thought my earlier code and changed it a little so use this code instead of the previous. When installed, each time you enter something in Cell C5 of that particular worksheet, the same value will be automatically added to the next empty cell below cell A10. Good luck, Tom Private Sub worksheet_Change(ByVal Target As Range) Dim rwRow as long If Not Intersect(Target, Range("C5")) Is Nothing Then rwRow = Range("A10").End(xlDown).Row Range("A" & rwRow + 1).Value = Range("C5").Value End If End Sub |