Add letter before content in a cell (excel macro)

Published:
Updated:
I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document.

My English is as it is, so I will try explain what it does diffrently.

If you have an excel document with 2000 rows and you need to add an "A" before every row under column "A", this is what you can do.

First you'll need to create a new macro in excel, when you do the "Microsoft Visual Basic Editor" will open.

In the editor, paste this code (also included below):
--------------------------------------
Sub AddLetterMacro()

For x = 1 To 2000

    ActiveCell.FormulaR1C1 = "THE LETTER" + ActiveCell.Text
   
    ActiveCell.Offset(1, 0).Select

Next x
End Sub
--------------------------------------

At this row:

For x = 1 To 2000

change the value "2000" to the number of cells in your document.

then go to:

ActiveCell.FormulaR1C1 = "THE LETTER" + ActiveCell.Text

and change "THE LETTER" to any letter you want to be placed before every cell in that row.

Now go to your Excel document and click in the cell where your should start, then go to the editor again and press the "Play(>)" button or just press "F5" and the macro will go off.

This macro could probably be done much better to count the rows in the document etc. but it's an easy way to go.

You could also add an letter to the end of the cell instead of the beginning by simply changing:

ActiveCell.FormulaR1C1 = "THE LETTER" + ActiveCell.Text

to

ActiveCell.FormulaR1C1 = ActiveCell.Text + "THE LETTER"

Hope this article will be to some use for you.

And also, this macro has been tested on both MS Excel 2003 and MS Excel 2007.

// Tobias
0
6,574 Views

Comments (4)

thnks for this article
i have to try
:)

Commented:
Yes it is helpful.Thanks for the article

jack
This seems to be a bit small for an article. The task itself is fairly simple and does not have a wide variety of application. It's for a very specific need and there are many ways of solving it with and without macros, including more efficient and more generic ways.

Furthermore, what the actual code is doing is taking the current displayed text in the active cell, and writing it into the formula of the active cell. These are very specific commands and will be broken by considerations such as whether the desired sheet is actually active at the time. Also, if the user clicks somewhere in the sheet while the program is running, it will break the formula and start adding the letter to cells below the one that the user clicked.
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
I agree that this article cannot qualify as the information is very basic and may be found on the net in some form or the other. Also as alainbryden has stated there would be many ways of solving it.

The author might cconsider looking into a variety of ways to accomplish this task and publish them together with their pros and cons.

Saqib

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community