How do I copy a cell to a "CELL+1" cell Every time a Button is press?

[nangel]

I am automating a process using excel. I created a button to execute a couple of commands. What I would like to do is to copy a cell to the cell next to it every time the button is press.

Example:

A B C D E F
1 t1 10
2 t2 20
3
4 ttl 30
5
First time after you press the button follows:

A B C D E F
1 t1 10
2 t2 20 30
3
4 ttl 30
5
The cell containers may change (Ex B1 from 10 to 20)
Second time after you press the button follows:
A B C D E F
1 t1 20
2 t2 20 30 40
3
4 ttl 40
5
The cell containers may change (Ex B1 from 20 to 5)
Third time after you press the button follows:
A B C D E F
1 t1 5
2 t2 20 30 40 25
3
4 ttl 25
5

Any comments or suggestions are welcome. Thank you very much for your help!

 

[johnwm]

You can keep track of the current column number by using a Static variable in the Command_Click event:

Private Sub CommandButton1_Click()
Static a As Integer
Dim myRange As Range
Set myRange = ActiveSheet.Cells(6, (a + 3))
myRange.Value = ActiveSheet.Cells(6, 2).Value
a = a + 1
End Sub

The Static retains it's value for the life of the active application. If you want to retain it between successive runs of the application, you'll need to store it in a cell and use it as the cell offset in the Set myRange statement.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[MortenA]

A suggestion that can be used for other tasks (assuming excel):

Use the macro recorder:

1) click tools|macro|record new macro
2) At the first input box choose a name for the macro (or keep the default) and a key for activating the macro
3) A small box appears with two buttons. The rightmost should be clicked once in order to activate "relative refernces
4) Perform you keystroke sequence (important: Only the ones required - no initial moving around since this will all be stored in the macro.
5) Click the stop button in order to stop recording keystrokes.

Now each time you press <ctrl> and your chosen key then the macro will be executed.

 

[nangel]

Johnwm - Thank you for your help.

Can you or anybody else, please explained the following code line by line?

Thank you in advance for your help:

Private Sub CommandButton1_Click()
Static a As Integer
Dim myRange As Range
Set myRange = ActiveSheet.Cells(6, (a + 3))
myRange.Value = ActiveSheet.Cells(6, 2).Value
a = a + 1
End Sub

 

[johnwm]

Private Sub CommandButton1_Click() 'Private means sub is only visible within this module
Static a As Integer ' Static works like Dim, but doesn't get reset until the whole module restarts
Dim myRange As Range ' variable for Range Object
Set myRange = ActiveSheet.Cells(6, (a + 3)) ' Set myRange to Row 6, column 3 on first click
myRange.Value = ActiveSheet.Cells(6, 2).Value ' assign value in Row 6 column 2 to previously selected cell
a = a + 1 ' increment a, so next click a will have value 1, and myRange will point to Row 6, Col 4
End Sub

To get started with VB try one of the beginners VB pages. There are some excellent starters around, like:

http://www.officecomputertraining.com/vbtutorial/tutpages/default.asp

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[nangel]

Thank you very much for your detail information!

 

[nangel]

Mr. johnwm - Thank you once again for your help. It is working just fine. I would like to make a little modification as per your suggestion.

How do I prompt the user to provide the cell where the next number should go? OR how do I refer a cell for its location?

Example:

A B C D E F
1 t1 5 E2
2 t2 20 30 40
3
4 ttl 25
5

In this example a message box will ask the user where does he want to copy the cell in 'B4' to. The user should answer'E2'

OR

Perhaps we can put the "E2" in cell 'F1' so when the program runs it will copy the value on 'B4' to whatever cell we specify in 'F1'. In this case 'E4'

After running this program the following will be the output!

A B C D E F
1 t1 5 E2
2 t2 20 30 40 25
3
4 ttl 25
5

One again - Thank you very much for your quick response and help.

 

[johnwm]

You can prompt the user with a InputBox command, which will get a string or numeric value for you to process.

It looks as if you're getting serious about this, in which case you really need to do some studying. Check out the tutorial site indicated above so you can get some basics. If you get stuck on specific code issues then post your code back here, with an explanation of the problem, and we will see if we can help. I'm sorry but I don't have time to write your application for you!



Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk