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:
- Introduction
- Setting up Excel for VBA
- Recording your first Macro (and challenge question)
- Answer to challenge question
- Reading your first Macro
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…
The finished report will look like this….
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
- Find your module in the Project window
- If needed, expand the “Modules” folder
- Double click on your module name (first defaults to “Module1”).
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
- 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 :
Range(“A1”).Select
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”
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
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!!!
