×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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!

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

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

CODE

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: www.essexsteam.co.uk

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

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.

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

(OP)
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

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

CODE

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: www.essexsteam.co.uk

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

(OP)
Thank you very much for your detail information!

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

(OP)
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.  

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

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: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close