Did you ever needed to swap values from one cell to another? Do you know how it is possible in Excel? Don’t worry if you want to do this and don’t know the process because in this tutorial I will tell you complete process with step by step by using Macros in Excel. In addition you will also get some programming along the tutorial. 

[icon icon=”refresh” url=”” size=”14px” new_window=”no”]Previously: How to Use Macros in Excel to Insert Multiple Columns

Before following the procedure of this tutorial make sure you have enabled the Excel’s Developer Tab.

STEP 1: firstly follow this to start creating you button:

Go to Developer > Insert > ActiveX Controls > Button

Swap Values In Excel

Now in the Excel spread-sheet, drag anywhere to get a new button.

Swap Values In Excel

STEP 2: In second step select View Code option by right clicking on your new button which you have created in your first step. 

Swap Values In Excel

STEP 3: Now you have open up a view code so here type this code:

Dim container As Double

container = Range(“A2”).Value
Range(“A2”).Value = Range(“B2”).Value
Range(“B2”).Value = container

Swap Values In Excel
Make Sure You Type the Whole CODE

Explanation of each code line is written below:

  • Dim container As Double

This code line will help you to create a new variable named which will be named as container inside that you can store a numerical value and double type shows that it can also have a numerical value.

  • container = Range(“A2”).Value

By this step in Cell A2 you are getting the first value and it will store it in your container variable.

  • Range(“A2”).Value = Range(“B2”).Value

Now you have to copy the content of Cell B2 so that it can get paste into Cell A2. Just after completing this; both will be having the value 500 in this given example.

  • Range(“B2”).Value = container

In the last lined step copy and paste the content of the container variable into Cell B2 so that the swap can be completed.

STEP 4: Ensure to deselect the Design Mode before testing it out.

Swap Values In Excel

STEP 5:  Now you can see the swapped values just after clicking on the button.

Swap Values In Excel