Excel VBA Macros : How To Change Text In A Cell

The Story So Far…

Our series of posts on Excel VBA Macros began with these posts:

If you have not already read them, I would suggest going through them now before continuing.  The last post contained a lot of information and don’t worry if you feel like it was too much to take in at once – over the coming weeks we are going to build up the Macro one piece at a time.

In prior posts, we were working on a report that had to be formatted.

The original format was like this…

Excel example to be used to demonstrate excel vba macros

The finished report will look like this….

How a report looks after using excel vba macros


Download this file, which you will be working from : Macros_Example_2


Excel VBA Macros : How To Change Text In A Cell

Create A Module

Before you start writing a Macro, you need somewhere to create it!

With the file open, go to your Visual Basic Editor (VBE) by pressing Alt+F11 together.

  • Go To “Insert”
  • Select “Module” from the menu

Screenshot of how to insert a module

  • Find your module in the Project window
  • If needed, expand the “Modules” folder
  • Double click on your module name (first defaults to “Module1”).

Screenshot of where module can be found for vba

You are now ready to start writing your Macro.


Create A Macro

All the Macros we will be dealing with for now will be created by typing “Sub”, then the Macro name, followed by two brackets”()”.

There are many options when it comes to creating Macros, for now we will be doing them all this way.

To create your macro

  • In your VBE, type “Sub” then the name of your macro followed by two brackets.  I’m going to name my Macro “mySecondMacro”
  • Hit the return key

Declaring a VBA Macro

  • Note that when you press the return key, a line is skipped and then you see “End Sub”.  This is the end of your Macro, you will type everything you need for the Macro between these two lines.

How To Change Text In A Cell

Code Snippet :


ActiveCell.FormulaR1C1 = “Whatever You Want”

To change the text in a cell, first select it.  In this example, cell A1 is being selected.

Then change the value in the active cell to whatever you want, just put it between double apostrophes.

Write The Code

In our project, we want to change the values in A1 to Item, B1 to Price, C1 to Quantity and D1 to Total.

We will select each cell, change the active cell formula to our new value and then move onto the next cell until all 4 are done.

Between your “Sub…” and “End Sub” lines, enter this code

  • Range(“A1”).Select
  • ActiveCell.FormulaR1C1 = “Item”
  • Range(“B1”).Select
  • ActiveCell.FormulaR1C1 = “Price”
  • Range(“C1”).Select
  • ActiveCell.FormulaR1C1 = “Quantity”
  • Range(“D1”).Select
  • ActiveCell.FormulaR1C1 = “Total”

macros how to change text in a cell

Test The Code

  • Close the VBE window
  • In your normal Excel window, copy Sheet1
  • In you copied sheet press Alt+F8 to run Macros
  • Select your Macro from the list and Run
  • You should see your headers change

Image of excel after running vba macro

TIP : We have 8 lines of code above, I don’t type out 8 lines of code, instead I write 2 lines and copy it 3 more times and then only change the parts of the code that I need to change.


What Next?

Next time we will do some formatting on our headers by changing font size, color, alignment, etc.

In The Meantime….

Can’t wait to get a bit more into coding?  Create your own file, it can be a blank file and you can use code to fill it in.

Try running the macro with Alt+F8, or see if you can remember how to create a button and assign the macro to it.

Have fun!!!

Click Here to Leave a Comment Below 1 comments

Get every new post delivered to your Inbox

Join other followers:

%d bloggers like this: