Solved Populate list from single cell

August 4, 2013 at 20:28:31
Specs: Windows 7
How can I populate a list from a single cell so the data in the single cell becomes the latest entry on the list, the other entries move down one and the oldest enrty on the list 'drops off' or is lost/overwritten

See More: Populate list from single cell

Report •


✔ Best Answer
August 8, 2013 at 21:15:52
re: The data is basic numbers that just happen to be related to those months. So system date may not be needed? I think?

Regardless of whether the system date is used or something else is used, the code stills needs somthing to tell it where to place the next value entered into K10 and when to replace the January value with a new one. Think about it logically. Once A31 through A42 are filled with the first 12 pieces of data, how will the VBA code know that the next entry into K10 needs to go into A31? The code can't read the user's mind, so it has to be told in some way.

Since the entry of the data is not actually date related, we need a method other than the system date. For that we can use a counter. Let the counter start at 1 for the first entry, add 1 to it each time K10 is changed and reset it to 1 as soon as it reaches 13. The counter will continually count from 1 to 12, which we can use to determine which row to place the K10 data in.

Now, since VBA can not store any values once it is done running after each change to K10, we need to store the counter in a cell in the spreadsheet so that it is always available for the VBA code to read. In addition, if we add 30 to the counter, then we can use that number as the row number for the placement of the data. In other words, when the counter reads 1, 1 + 30 is 31, so the code will place the new data in A31. when the counter reads 12, 12 + 30 is 42, so the code will place the new data in A42. The next time K10 is changed, the counter will reach 13, be reset to 1, 1 + 30 is 31 and the data will be placed in A31, overwriting the current value in that cell.

The counter can be placed in any cell, it can be hidden, it can placed out of the normal viewing range, etc. For the purposes of this example, I put the counter in L10, so you can see it change whenever you enter a value in K10.

The only issue with this method is that if the counter is changed by the user it will screw up the VBA code since the counter is used to determine the next row number for the data placement. That problem can be solved by locking the cell with the counter, unlocking all other cells and then password protecting the worksheet if need be.

Try this code and let me know if it accomplishes your goal.

Private Sub Worksheet_Change(ByVal Target As Range)
'Make sure only the Target cell (K10) has changed
   If Target.Address = "$K$10" And Target.Cells.Count = 1 Then
'Increment L10 counter, Reset it when it reaches 13
      Cells(10, 12) = Cells(10, 12) + 1
        If Cells(10, 12) = 13 Then Cells(10, 12) = 1
'Use L10 counter value to copy Target to correct row (31 To 42)
       nxtRow = Cells(10, 12) + 30
         Cells(nxtRow, 1) = Target
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 5, 2013 at 07:41:10
Right-click the sheet tab for the sheet you want this to happen in, choose View Code, and Paste the code found below into the pane that opens.

Since you didn't provide any information about which cell you will be changing, how long the list is, or where the list is located, I made the following assumptions when writing the code:

1 - You will enter your new data in A1
2 - Your List will contain 10 items
3 - The list is located in B1:B10 on the same sheet



Private Sub Worksheet_Change(ByVal Target As Range)
'Make sure only the Target cell (A1) has changed
   If Target.Address = "$A$1" And Target.Cells.Count = 1 Then
'Move current list down one row; last entry will be lost
     For oldRw = 10 To 2 Step -1
       Cells(oldRw, 2) = Cells(oldRw - 1, 2)
     Next
'Copy A1 to B1
       Cells(1, 2) = Target
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 6, 2013 at 01:34:18
DerbyDad03

Thanks a lot

How do I get the entries to move across in a row rather than down? – sorry I am very new to VBA.


Report •

#3
August 6, 2013 at 06:32:08
This version of the code will populate B1:K1 with your list. Entries are still made into A1.

Private Sub Worksheet_Change(ByVal Target As Range)
'Make sure only the Target cell (A1) has changed
   If Target.Address = "$A$1" And Target.Cells.Count = 1 Then
'Shift current list by one column; last entry will be lost
     For oldCol = 11 To 2 Step -1
       Cells(1, oldCol) = Cells(1, oldCol - 1)
     Next
'Copy A1 to B1
       Cells(1, 2) = Target
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 6, 2013 at 18:07:07
DerbyDad03

I actually want to:-

use cell K10 as the data entry cell
List will contain 12 entries
List will be located in N10 to Y10

Thanks


Report •

#5
August 6, 2013 at 20:13:35
Private Sub Worksheet_Change(ByVal Target As Range)
'Make sure only the Target cell (K10) has changed
   If Target.Address = "$K$10" And Target.Cells.Count = 1 Then
'Shift current list by one column; last entry will be lost
     For oldCol = 25 To 13 Step -1
       Cells(10, oldCol) = Cells(10, oldCol - 1)
     Next
'Copy K10 to N10
       Cells(10, 14) = Target
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 6, 2013 at 23:44:04

DerbyDad03
Sorry - change of plan - i wont bother you again (fingers crossed in hope) now I have to do the following:-

use cell K10 as the data entry cell
List will contain 12 entries
List will be located in A31 to A42

Your help to date is very much appreciared.



Report •

#7
August 7, 2013 at 00:53:36
DerbyDad03

How would I do the above but the data from each entry goes into the next data cell (I.E. Jan, Feb, March....etc to Dec) so the data does not overight the previous data but after Dec the next input overights the perviouse years Jan etc.

use cell K10 as the data entry cell
List will contain 12 entries
List will be located in A31 to A42

Is this possible?


Report •

#8
August 7, 2013 at 06:49:39
Please allow me to offer a posting tip for when you ask for help in a forum such as computing.net. While the wording may sound harsh, please take these comments in the spirit in which they are intended.

You should try to include as much detail as you can in your original post so that those of us that want to offer suggestions know what the requirements are. Yes, the help here is free, but those of us that volunteer to offer solutions typically have real jobs and families and other projects. We want to offer suggestions that meet your requirements, but we really don't like spending time modifying our suggestions each time more or different requirements are posted.

Imagine if you were paying a consultant and repeatedly changed the requirements each time they made a proposal. You would typically be charged for each change request, so it is more cost effective to have all the requirements laid out up front. "Free help" should be handled the same way. While it isn't costing you anything, it is costing the volunteers time and effort to make these changes.

In this case, the first 2 modifications were simple, but your latest request is much more complicated and will require a different strategy, essentially starting from scratch.

In order to overwrite the previous year's monthly data, the code needs to know which month to overwrite. While the previous code reacted to any change to K10 and simply shifted the the data to the left, your lastest change isn't that simple. Something has to tell the code what month to change.

This can be done by checking the system date if the change to K10 will only be done once a month, but it would need to know if it should change the current month or last month or the next month, etc. It could also be done by checking a cell in the worksheet, e.g. a cell that contains a date or the month that should be overwritten in the list. The bottom line is that you can't overwrite a specific month with just a change to K10. There must be another test done in order for the code to know which month should be overwritten.

If you could supply a little detail as to how to the spreadsheet is laid out and used, that might help. If you plan to post example data, please click on the following line and read the instructions on how to post data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#9
August 7, 2013 at 20:13:59
DerbyDad03
I sincerely apologise and do very much appreciate your assistance, this is the first time I have used any such site like this, and whilst it is no excuse for my failure to communicate accurately in the past, I have read and understood your message clearly and have learned from it – thanks again.

Please allow me to explain further:-

I wish to use cell K10 as the data entry cell.
Each once a month entry will be stored one at a time in the 12 cells A31 to A42 (January to December). The first entry is to be stored in A31 and will be the data for January, the second entry will be stored in A32 and will be the data for February, and the 3 entry for March will be stored in A33 etc until the 12th entry data for December will be stored in A42.

When the 13th entry is made (January of the next year) the entry over wrights last year’s January data held in cell A31. The fourteenth entry (February of the next year) over wrights last year’s February’s data held in cell A32 and each subsequent entry over wrights the previous year’s data held for the following months. The 24th entry over wrights the previous year’s December entry held in cell A42.

This should then enable the user to make the monthly entries for all forthcoming years so each monthly entry over wrights the previous data held for the corresponding month of the previous year.

I do hope this is clear – and thanks once again


Report •

#10
August 8, 2013 at 05:10:33
Did you read the last three paragraphs of my last response?

I know what you trying to do, but I can't offer any suggestions until I know more about how your spreadsheet is used. As I said, Excel needs to know what month the latest entry in K10 applies to.

For example, if the January entry is always entered during the month of January, then the code can use the system date and know to copy the K10 value to A31. However, if the January value is always entered into K10 during February (i.e. after the month is over) then code would need to subject 1 month from the current system date to have the data overwrite the old January data.

I need to know how the VBA code will know what month to overwrite, so I need to know more about when the K10 data will be changed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
August 8, 2013 at 18:19:04

I did read your last three paragraphs with interest, but whilst the data happens to be related to the months it is not necessarily date data I.E. the data can be entered at any time. The data is basic numbers that just happen to be related to those months. So system date may not be needed? I think?
The last entry will relate to the last input in the perpetual sequence of Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar …etc. (it could relate to any 12 titles of data actually) e.g. entry 1 will be in cell A31, entry 2 in cell A32, entry 3 in cell A33 etc. entry 12 will be in cell A42.
Entry 13 will over wright the data in cell A31, entry 14 will over wright the data in cell A32, etc. entry 24 will over wright the data in cell A42, etc.
Entry 25 will over wright the data in cell A31, entry 26 will over wright the data in cell A32, etc. entry 36 will over wright the data in cell A42, etc.
This unending 12 cycle of data inputs will always start input at A31 and fill cells A31 to A42 continuously over righting the previously held data (after the first 12 inputs of course )
PS: is there any way (if this is OK and allowed) that I can contact you directly?

Report •

#12
August 8, 2013 at 21:15:52
✔ Best Answer
re: The data is basic numbers that just happen to be related to those months. So system date may not be needed? I think?

Regardless of whether the system date is used or something else is used, the code stills needs somthing to tell it where to place the next value entered into K10 and when to replace the January value with a new one. Think about it logically. Once A31 through A42 are filled with the first 12 pieces of data, how will the VBA code know that the next entry into K10 needs to go into A31? The code can't read the user's mind, so it has to be told in some way.

Since the entry of the data is not actually date related, we need a method other than the system date. For that we can use a counter. Let the counter start at 1 for the first entry, add 1 to it each time K10 is changed and reset it to 1 as soon as it reaches 13. The counter will continually count from 1 to 12, which we can use to determine which row to place the K10 data in.

Now, since VBA can not store any values once it is done running after each change to K10, we need to store the counter in a cell in the spreadsheet so that it is always available for the VBA code to read. In addition, if we add 30 to the counter, then we can use that number as the row number for the placement of the data. In other words, when the counter reads 1, 1 + 30 is 31, so the code will place the new data in A31. when the counter reads 12, 12 + 30 is 42, so the code will place the new data in A42. The next time K10 is changed, the counter will reach 13, be reset to 1, 1 + 30 is 31 and the data will be placed in A31, overwriting the current value in that cell.

The counter can be placed in any cell, it can be hidden, it can placed out of the normal viewing range, etc. For the purposes of this example, I put the counter in L10, so you can see it change whenever you enter a value in K10.

The only issue with this method is that if the counter is changed by the user it will screw up the VBA code since the counter is used to determine the next row number for the data placement. That problem can be solved by locking the cell with the counter, unlocking all other cells and then password protecting the worksheet if need be.

Try this code and let me know if it accomplishes your goal.

Private Sub Worksheet_Change(ByVal Target As Range)
'Make sure only the Target cell (K10) has changed
   If Target.Address = "$K$10" And Target.Cells.Count = 1 Then
'Increment L10 counter, Reset it when it reaches 13
      Cells(10, 12) = Cells(10, 12) + 1
        If Cells(10, 12) = 13 Then Cells(10, 12) = 1
'Use L10 counter value to copy Target to correct row (31 To 42)
       nxtRow = Cells(10, 12) + 30
         Cells(nxtRow, 1) = Target
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
August 8, 2013 at 22:36:07
Derbydad03

It is perfect and surpasses all my expectations – I am delighted, it is great. I wish I under stood it in detail, I really do. I am impressed.

PS: is there any way (if this is OK and allowed) that I can contact you directly?


Report •

#14
August 9, 2013 at 04:15:32
You can send me a Private Message by clicking on my user name and then clicking on my username again on the next screen.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question