Swapping Two Values using Macros in Excel

This post explains how you can swap two values in Microsoft Excel using macros. You can swap two values in Excel using different methods. Here we are going to see in detail about swapping values using macros.

Before that let’s see what is macro in excel.

What is an Excel Macro?

Excel Macro is a record and playback tool that records your Excel steps and you can playback it as many times. VBA (Visual Basic Applications) Macros saves time. Macros are a set of programming codes that runs in an Excel environment.

Swapping Two Values using Macros in Excel

Now to swap the values, follow these steps:

  • Open your Excel sheet.
  • Under the Developer tab click Insert
Swapping Two Values using Macros in Excel
  • In the new submenu, click ActiveX Controls and choose the Button in it.
  • Drag and place your button anywhere on your excel sheet.
  • Now you need to enter the code for swapping the values.
  • Right-click on your new button and select the View Code option.
Swapping Two Values using Macros in Excel

To swap the values, enter the following code:

Dim container As Double
container = Range("A2").Value
Range("A2").Value = Range("B2").Value
Range("B2").Value = container

Let’s break the code in detail.

Dim container As Double

  • Here it creates a variable called container where it stores a numerical value in it The double type can store a numerical value.

container = Range(“A2”).Value

  • So the value of A2 gets stored in the container.

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

  • This will now copy the data stored in cell B2 and paste it into Cell A2.

Range(“B2”).Value = container

  • This step will Copy the data stored in the container variable into Cell B2.
  • That’s it, swapping two variables between cells using macros is completed successfully.
  • To run this, uncheck the design mode and click Run.
  • Now, Click the button and check for the values getting swapped.