Solved How can I make a cell shift along one when i hit a button?

June 17, 2015 at 12:05:07
Specs: Windows 7
I have created a code that runs a clock in cell A1 and when I hit a button the value of A1 is saved in a given cell (e.g. C1) What I would like to do now is hit the same button but have the new clock value save in C2, then C3 and so on.

The current code works well but I cannot think how to progress the cell value! I know it should be simple but it's not happening right now.

Any help is greatly appreciated, thanks.

The code I am using under the button is currently:

Worksheets("Sheet1").Range("C4").Value = Worksheets("Sheet1").Range("A1")

message edited by Higart


See More: How can I make a cell shift along one when i hit a button?

Report •


#1
June 17, 2015 at 12:15:13
Assuming that you want the value to be placed in the next empty cell in Column C, and that there is no data below that cell, all you need to do is determine the last Row with data in Column C and then add 1 to that value:

   lastRw = Range("C" & Rows.Count).End(xlUp).Row
   nxtRw = lastRw + 1
    'or
   nxtRw = Range("C" & Rows.Count).End(xlUp).Row + 1

    Range("C" & nxtRw).Value = Range("A1")

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


Report •

#2
June 17, 2015 at 12:41:18
Yes, thank you. I actually meant to go along columns rather than rows so C4, D4, D5 etc.

Strangely, using the code only copied the data into the next row down and then stopped. Shouldn't this keep going indefinitely?

I'm assuming that I should be able to replace "Row" with "Column"? what would nxtRW become in this instance?

Thanks for the help, I'm more accustomed to programming Robotics in C so the terminology is slightly different.

To give you an idea of what I am trying to do, we run a club meet with multiple riders who complete laps of a circuit. At the moment, old school pen and paper is used to record rider lap times. I thought I could create a spread sheet which can record rider times with the simple press of a button. I've made it work with multiple buttons but now I want to simplify down to one which is where I have become a little stuck!


Report •

#3
June 17, 2015 at 13:03:51
✔ Best Answer
re: I'm assuming that I should be able to replace "Row" with "Column"? what would nxtRW become in this instance?

Not exactly. The following code will find the next empty Column in Row 1 and place the value from A1 into the next empty cell in Row 1.

    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    nxtCol = lastCol + 1

    'or
    'nxtCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

    Cells(1, nxtCol) = Range("A1")

re: Strangely, using the code only copied the data into the next row down and then stopped. Shouldn't this keep going indefinitely?

There is nothing in the code that tells it to loop. If you have the code assigned to a "button", it simply executes the instructions once per click. If you want it to continually place the time in the next empty cell, you need to loop it, or call it from the timer macro each time A1 updates.

Something has to tell the code to place the updated time in the next Column - either the user or some other piece of code.

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


Report •

Related Solutions

#4
June 17, 2015 at 13:29:20
That's brilliant. I've being playing with the code and managed to make it work exactly as I wanted in vertical rows, I now just need to convert it to columns.

The idea is to update on the click of a button rather than automatically. I'm not sure why it was only placing one instance of the timer but I have managed get it to update in multiple rows now. I'm thinking that I had data in a cell that I shouldn't have and it was stopping because of this.

Thank you very much for your help. I'll be testing this next Tuesday at our next club meet - fingers crossed!


Report •


Ask Question